Why My Unique Index Doesn't Work in Postgresql?

Question

I created a table for to store day off data of users. After, set a unique index for multi column but when exec insert sql, it duplicates datas again

Here's my 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
);

Index:

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

Control query:

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

Output as 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
  }
]

What is my wrong? Thanks for your helps.

You are aware that that index won't give an error if two rows have the same `user_id`? Only if *all* columns are the same, the unique index will give you an error. — Laurenz Albe, Aug 04 '23 at 09:37

score 5 · Accepted Answer · answered Aug 04 '23 at 09:47

It appears that you've created a unique index on the columns user_id, proxy_user_id, day_off_start_date, and day_off_end_date. This means that the combination of these four columns should be unique in your table. However, in your JSON, you have a null value for proxy_user_id.

In SQL, null is not considered a value, and it does not participate in uniqueness constraints in the same way that other values do. This means that multiple rows with null in the proxy_user_id field will not violate the unique index, even if the other fields (user_id, day_off_start_date, and day_off_end_date) are the same.

If you want to prevent this, you have a few options:

Disallow nulls in the proxy_user_id field
Use a placeholder value instead of null values in JSON
Use a partial index

Here's an example of how you might create a partial index:

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;

This would enforce the uniqueness constraint for all rows where proxy_user_id is not null, but would allow multiple rows with the same user_id, day_off_start_date, and day_off_end_date if proxy_user_id is null.

OMG! I didn't realize. Thanks for solution and kindly, clear explanation. — icsarisakal, Aug 04 '23 at 11:04
Another option, new in v15, is putting `NULLS NOT DISTINCT` on the index. — jjanes, Aug 04 '23 at 11:15

score 5 · Answer 2 · answered Aug 04 '23 at 09:57

5

Null values are not considered equal to any other value, including other nulls (or even itself!). This is not peculiar to Postgres: It's the SQL standard.

answered Aug 04 '23 at 09:57

Bohemian

412,405
93
575
722

Unless you put ‘nulls not distinct ‘ in the constraint definition. Available since version 15 – Frank Heikens Aug 04 '23 at 14:10

Why My Unique Index Doesn't Work in Postgresql?

2 Answers2