Postgresql: conditionally unique constraint

Question

I would like to add a constraint that ensures uniqueness in a column only in part of the table.

ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null); 

The part above WHERE is wishful thinking.

Any way to do this? Or do I need to go back to the relational drawing board?

Answer 1

PostgreSQL does not define a partial (i.e. conditional) UNIQUE - however, you can create a partial unique index. PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, you just won't see the constraint specified in information_schema .

 CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null); 

See partial indexes .

Answer 2

It has already been said that PG does not define a partial (i.e., conditional) unique constraint. The documentation also says that the preferred way to add a unique constraint to a table is ADD CONSTRAINT unique indexes.

The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. Using indexes to provide unique constraints can be seen as an implementation detail that cannot be accessed directly. However, remember that there is no need to manually create indexes for unique columns; it just duplicates the automatically generated index.

There is a way to implement this with exclusive restrictions (thanks @dukelion for this solution)

In your case, it will look like this

 ALTER TABLE stop ADD CONSTRAINT stop_col_a_key_part EXCLUDE (col_a WITH =) WHERE (col_b IS null);