Create a unique constraint where one column can be null to prevent duplicate record insertion [duplicate]

Question

In Postgres 13.4, trying to create a unique constraint where a record in a cars table is unique by name and user_id. The purchased_at date starts out as NULL but can change to a value once a car is purchased. I run the following but get a syntax error:

ALTER TABLE cars ADD CONSTRAINT cars_user_id_name_purchased_at_key UNIQUE (user_id, name) WHERE purchased_at IS NULL

Error:

ERROR 42601 (syntax_error) syntax error at or near "WHERE"

What is the issue and how to fix?

Wait, so once a car is purchased you want to lift the uniqueness constraint? — Bergi
– Bergi, Commented Oct 20, 2021 at 1:25
I changed the names (and hence the domain model) to obscure the production system a bit; but essentially you're creating a new car by name (tied to a user though, so 2 different users could have the same car name in this system) and we didn't want to allow duplicate names. Think of "purchased_at" as doing a soft delete in the new car inventory but tied to a user. — Nona
– Nona, Commented Oct 20, 2021 at 4:00
You need a unique index in order to be able to use a WHERE clause. This is not possible for a unique constraint — user330315
– user330315, Commented Oct 20, 2021 at 5:29
@Nona But can a user create another car with the same name after they soft-deleted the first? — Bergi
– Bergi, Commented Oct 20, 2021 at 9:49

Laurenz Albe · Accepted Answer · 2021-10-20 06:27:22Z

You cannot have a WHERE clause in a constraint definition, but a partial unique index will do the trick:

CREATE UNIQUE INDEX ON cars (user_id, name) WHERE purchased_at IS NULL;

Collectives™ on Stack Overflow

Create a unique constraint where one column can be null to prevent duplicate record insertion [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related