In Postgres, most constraints work on a single row, like check and not-null. Where we finally start to work across multiple rows is with unique constraints.

You can read a unique constraint as â€śno two rows have the same value for these columnsâ€ť.

Partial Indexes

Now, you can constrain columns for a subset of rows by using a partial index.

Handling of Nulls

No discussion of postgres unique constraints would be complete without mentioning nulls not distinct. (Note - I recently learned Iâ€™m not qualified to use nulls).

By default, two null values are not considered equal in a unique constraint.

This behavior can be changed with nulls not distinct.

Exclusion Constraints

An exclusion constraint is a generalized version of a unique constraint. They are all about preventing overlap between two rows, any form of overlap.

Reimplement a Unique Constraint With an Exclusion Constraint

However, with an exclusion constraint, we can use any operator that is: