PostgreSQL constraint with Null Value

user_11268322 0
Viewed 121

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

unique-constraint postgresql
3 Answers

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

try to use EXCLUDE

ALTER TABLE my_table
    ADD CONSTRAINT excl1 EXCLUDE USING btree (
    seller_id ASC NULLS LAST WITH =,
    product_id ASC NULLS LAST WITH =,
    COALESCE(sold_out_date, '2020-01-01') ASC NULLS LAST WITH =);

and ON CONFLICT DO NOTHING will work

user_120815430

It is a common practice in databases to use id's based on hashes, so you could create a hash on the combination of those three fields and use it as an id, then create a primary key constraint over this id field, e.g.

    INSERT INTO my_table
(seller_id, product_id, sold_out_date, id)
VALUES (1, 'A', null, encode(digest(concat('1','A',null) , 'sha1'),'base64'))
ON CONFLICT DO NOTHING;

it is necessary that pgcrypto extension exists in your database, if don't use:

create extension pgcrypto;
user_150623610
Related