0

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?

Nona
  • 5,302
  • 7
  • 41
  • 79
  • Wait, so once a car is purchased you want to lift the uniqueness constraint? – Bergi Oct 20 '21 at 01: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 Oct 20 '21 at 04:00
  • You need a unique _index_ in order to be able to use a WHERE clause. This is not possible for a unique constraint –  Oct 20 '21 at 05:29
  • @Nona But can a user create another car with the same name after they soft-deleted the first? – Bergi Oct 20 '21 at 09:49
  • @Bergi - yes they can – Nona Oct 21 '21 at 02:11

1 Answers1

0

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;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263