Postgresql: Conditionally unique constraint
constraints, postgresql, unique-constraint
Solution
PostgreSQL doesn't define a partial (i.e. conditional) `UNIQUE` constraint - however, you can create a partial unique index.
PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can't perform upserts (`ON CONFLICT DO UPDATE`) against a unique index like you would against a unique constraint. (Edit: apparently you can use it with ON CONFLICT now)
Also, you won't see the constraint listed in `information_schema`.
CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);
See partial indexes.
Problem
I'd like to add a constraint which enforces uniqueness on a column only in a portion of a table. ``` ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null); ``` The `WHERE` part above is wishful thinking. Any way of doing this? Or should I go back to the relational drawing board?