CODEWITHSUNDEEP
postgresqlconstraintsunique-constraint
EoghanM
EoghanM

Reputation: 26914

Postgresql: Conditionally unique 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?

Upvotes: 204

Views: 113502

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324265

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.

Upvotes: 351

Peter Yeremenko
Peter Yeremenko

Reputation: 1279

it has already been said that PG doesn't define a partial (ie conditional) UNIQUE constraint. Also documentation 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. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

There is a way to implement it using Exclusion Constraints, (thank @dukelion for this solution)

In your case it will look like

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

Upvotes: 78

Related Questions