PostgreSQL constraint with Null Value

Question

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;

Answer 1

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';