Postgresql: Conditionally unique constraint

constraintspostgresqlunique-constraint

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?

Best Answer

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.

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.

Related Topic