1

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

Improve this question

4 Answers 4

Reset to default
2

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';
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

i guess the ON CONFLICT DO NOTHING will be problematic with such index
@ИгорьТыра what problem? see fiddle
This works, thanks! What if I want to update some other attributes in other columns on conflict. ON CONFLICT DO UPDATE ? DO UPDATE is asking for ON CONFLICT DO UPDATE requires inference specification or constraint name. How do I pass this index name here?
@Nihilist hmm in that case you can mention unique index columns in your conflict name, see updated fiddle
Great! Thanks for answers.
|
1

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

Improve this answer

3 Comments

exclude is much slower than unique , and behind scene make an index constraint for it.
yes, but it will work even after moving from ON CONFLICT DO NOTHING to ON CONFLICT ON CONSTRAINT excl1 DO UPDATE SET... if needed in future
Thanks for reply. Its not working with DO UPDATE ON CONFLICT DO UPDATE not supported with exclusion constraints
1

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;
Improve this answer

1 Comment

Thank you! I didn't try this but looks good. This may come handy in future.
0

For PG v15 and later, another option is NULLS NOT DISTINCT:

CREATE UNIQUE INDEX ux_indx
ON my_table(seller_id, product_id, sold_out_date)
NULLS NOT DISTINCT;

This reverses the usual behavior of treating all NULL values as unequal.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.