PostgreSQL UNIQUE Constraint

A PostgreSQL UNIQUE constraint prevents duplicate key values in a table. It can cover one column or a combination of columns. PostgreSQL automatically creates a unique B-tree index to enforce the constraint.

For a range-based key where periods for the same identifier must not overlap, PostgreSQL 18 also supports WITHOUT OVERLAPS. See the guide to temporal constraints.

A unique constraint differs from a PRIMARY KEY: a table can have multiple unique constraints, and their columns may allow NULL. For partial uniqueness or uniqueness on an expression, use a unique index instead.

Define a unique constraint

You can define a single-column constraint in the column definition or name it in a table constraint:

CREATE TABLE users (
    user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text,
    CONSTRAINT users_email_key UNIQUE (email)
);

The constraint rejects a second row with the same non-NULL email. A composite constraint makes the combination unique, while allowing each individual column value to appear in other combinations:

CREATE TABLE user_hobbies (
    user_id bigint NOT NULL,
    hobby text NOT NULL,
    UNIQUE (user_id, hobby)
);

This permits one user to have several different hobbies and several users to share the same hobby, but rejects a repeated (user_id, hobby) pair.

Add or remove a constraint

Add a constraint to an existing table with ALTER TABLE. PostgreSQL rejects the command if existing rows already violate it:

ALTER TABLE users
ADD CONSTRAINT users_email_key UNIQUE (email);

Remove a named constraint with:

ALTER TABLE users
DROP CONSTRAINT users_email_key;

Dropping a unique constraint also drops the index PostgreSQL created for it. If you need an index without a constraint, see the unique index tutorial.

How unique constraints treat NULL

By default, PostgreSQL treats NULL values as distinct for uniqueness checks. As a result, a single-column unique constraint can contain multiple NULL values. In a composite constraint, rows with NULL in any key column are also treated as distinct by default.

PostgreSQL 15 and later support NULLS NOT DISTINCT when NULL should count as equal:

CREATE TABLE contacts (
    email text,
    UNIQUE NULLS NOT DISTINCT (email)
);

With this constraint, at most one row can have a NULL email. It does not make the column NOT NULL; use both constraints if every row must have an email:

email text NOT NULL UNIQUE

Unique constraint or unique index?

Use a unique constraint to express a table-level rule on one or more columns. PostgreSQL creates the enforcing unique index automatically, so do not create a second index for the same columns.

Use a unique index when the rule needs an expression, a predicate that applies to only some rows, or another index-specific option. Read the PostgreSQL unique index guide for those cases.

For complete syntax and behavior, see PostgreSQL’s official unique constraint documentation.