Postgresql: Conditionally unique constraint

Question

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?

Islas

Commonly done. See "partial unique index" – Clutch

@yvesonline no, that's a regular unique constraint. The poster wants a partial unique constraint. – Clutch

Answer 1

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.

Clutch

Super! Unintuitive that the "constraint" doesn't show up as a constraint, but nonetheless gives the desired error of ERROR: duplicate key value violates unique constraint "stop_myc" – Islas

It's worth to be noted that this won't allow creating FKs referencig that partially unique field. – Leuko

It's also worth noting that this index effects can't be deferred. If you need to perform bulk updates this may present a problem as the uniqueness is checked after every row, not after the statement like it would be for a constraint or after the transaction like it would be for a deferrable constraint. – Barner

Note, that you can't use this in ON CONFLICT expressions, because it requires actual constraint on table, not just index. – Plutonic

Implementing this in Doctrine ORM framework for PHP via annotations would look like this: @Table(name="ecommerce_products",uniqueConstraints={@UniqueConstraint(name="stop_myc", columns={"col_a", "col_b"}, options={"where": "(col_b IS NOT NULL)"})}) as per docs: doctrine-project.org/projects/doctrine-orm/en/2.8/reference/… – Swoosh

You apparently can use partial unique indexes in ON CONFLICT DO UPDATE, you just have to basically redeclare the signature of the index. In your example, it would be ON CONFLICT (col_a) WHERE (col_b is NOT null) DO UPDATE. I'm on version 12; not sure when it was added. – Laure

For me this trick with on conflict .. where .. clause works on Postgres 11. Thank you! – Maverick

Answer 2

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);

Vanover

on that approach you don't use "using" to define index method, so that can be extremely slow or postgres creates a default index on that? That method is the canonical choice, but not ever the better choice! I think you gonna need a "using" clause with index to make that choice be the better one. – Interlinear

While slower, the advantage of the exclude solution is that it is deferrable (and by default defers until the end of the statement). In contrast, the accepted unique index solution cannot be deferred (and is checked after every row change). So a bulk update is often not possible because steps during the update would violate the unique constraint, even if it wouldn't be violated at the end of the atomic update statement. – Barner

This note was removed from the docs in august 2015 – Vimineous