为什么我的唯一索引在PostgreSQL中不起作用？

Question

我创建了一个表来存储用户的休假数据。之后，为多列设置了唯一索引，但在执行插入SQL时，它又重复了数据

这是我的DDL：

create table day_off_movements
(
    id                 bigserial
        primary key,
    user_id            bigint
        constraint fk_day_off_movements_user
            references users,
    proxy_user_id      bigint
        constraint fk_day_off_movements_proxy_users
            references users,
    reason             text,
    day_off_start_date timestamp with time zone,
    day_off_end_date   timestamp with time zone,
    used_days          bigint,
    created_at         timestamp with time zone,
    updated_at         timestamp with time zone
);

目录：

create unique index idx_day_off_all
    on day_off_movements (user_id, proxy_user_id, day_off_start_date, day_off_end_date);

控制查询：

SELECT user_id,proxy_user_id,day_off_end_date,day_off_start_date,count(*)
                 FROM public.day_off_movements t
GROUP BY user_id, proxy_user_id, day_off_end_date, day_off_start_date

以JSON格式输出：

[
  {
    "user_id": 961,
    "proxy_user_id": null,
    "day_off_end_date": "2020-07-29 00:00:00.000000 +00:00",
    "day_off_start_date": "2020-07-27 00:00:00.000000 +00:00",
    "count": 3
  }
]

我的错在哪里？ 谢谢你们的帮助。

- icsarisakal

---

你知道如果两行具有相同的user_id，那个索引不会报错吗？只有当所有列都相同时，唯一索引才会报错。 - Laurenz Albe

Answer 1

看起来你在列user_id、proxy_user_id、day_off_start_date和day_off_end_date上创建了一个唯一索引。这意味着这四个列的组合在你的表中应该是唯一的。然而，在你的JSON中，proxy_user_id有一个null值。

在SQL中，null不被视为一个值，并且它不像其他值那样参与唯一性约束。这意味着即使其他字段（user_id、day_off_start_date和day_off_end_date）相同，具有null值的proxy_user_id字段的多行也不会违反唯一索引。

如果你想要防止这种情况发生，你有几个选择：

• 禁止proxy_user_id字段为空
• 在JSON中使用占位符值代替null值
• 使用部分索引

这是一个创建部分索引的示例。

CREATE UNIQUE INDEX idx_day_off_all
ON day_off_movements (
    user_id, 
    proxy_user_id, 
    day_off_start_date,
    day_off_end_date
)
WHERE proxy_user_id IS NOT NULL;

这将对所有proxy_user_id不为空的行强制执行唯一性约束，但如果proxy_user_id为空，则允许具有相同user_id、day_off_start_date和day_off_end_date的多行。

- Iurii Tkachenko

---

哇！我没意识到。谢谢你的解决方案和清晰的解释。 - icsarisakal

另一个选项是在索引上使用NULLS NOT DISTINCT，这是v15中的新功能。 - jjanes

Answer 2

空值不被视为等于任何其他值，包括其他空值（甚至是它自己！）。这并不是Postgres特有的：这是SQL标准。

- Bohemian

---

除非在约束定义中加入“nulls not distinct”，否则无法使用。此功能从15版本开始可用。 - Frank Heikens