postgresqlconstraintsunique-constraint

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?

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.