Question 223023 in PostgreSQL Question posted in
The official documentation can be found here.

Why My Unique Index Doesn't Work in Postgresql?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search