Search code examples
postgresqlunique-constraint

PostgreSQL constraint with Null Value

I have this table. I need to make combination of [seller_id, product_id, sold_out_date] unique, but the problem is

CONSTRAINT u_product UNIQUE (seller_id, product_id, sold_out_date)

doesn't work to stop duplicate rows due to NULL values. Making it as primary key will not work either, as it can't be NULL.

The reason why I need it to be unique is when product is sold out, sold_out_date will be entered, and when the product comes back in stock, I need to create a new row, so the combination of all three has to be unique.

After creating this constraint I want to execute query like this:

INSERT INTO my_table
(seller_id, product_id, sold_out_date)

VALUES (1, 'A', NULL)

ON CONFLICT DO NOTHING;

enter image description here

Solution

  • instead add an unique index like so and you will be fine

    create unique index ux_indx on my_table(seller_id, product_id, coalesce(sold_out_date,'1990-01-01'));

    db<>fiddle here

    in case you need to update on conflict :

    INSERT INTO my_table
VALUES (1, 'A', NULL)
ON CONFLICT (seller_id, product_id, coalesce(sold_out_date,'1990-01-01')) DO UPDATE set sold_out_date = '2020-01-10';