How do you add a unique constraint in PostgreSQL?

Run ALTER TABLE t ADD CONSTRAINT t_email_key UNIQUE (email);. PostgreSQL builds a unique index behind the constraint and holds an ACCESS EXCLUSIVE lock for the whole build. On a busy table, build the index first with CREATE UNIQUE INDEX CONCURRENTLY, then attach it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX, which locks only briefly.

Key facts

| Fact | Value | |---|---| | What a unique constraint actually is | A unique B-tree index plus a catalog entry; "adding a unique constraint will automatically create a unique B-tree index" (PostgreSQL docs, unique constraints) | | Lock taken by ALTER TABLE ... ADD CONSTRAINT ... UNIQUE | ACCESS EXCLUSIVE, held for the whole index build (ALTER TABLE) | | Lock taken by CREATE UNIQUE INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE — reads and writes continue (CREATE INDEX) | | NULL handling by default | Nulls are treated as distinct, so many NULLs are allowed in a unique column (unique constraints) | | NULLS NOT DISTINCT | Available since PostgreSQL 15 on unique constraints and unique indexes (CREATE TABLE) | | Partitioned tables | A unique constraint on a partitioned table must include all partition key columns (partitioning limitations) | | Deferrable constraints | DEFERRABLE unique constraints cannot arbitrate ON CONFLICT DO UPDATE (CREATE TABLE) | | Fleet signal | Across 31 monitored instances, 67.7% fail the duplicate-index check; across 28 instances scored for schema, 57.1% have at least one table with no primary key (MyDBA aggregate health stats, 2026-09-17) |

Why this happens

A unique constraint in PostgreSQL is not a separate enforcement engine. The documentation is explicit that adding a unique constraint automatically creates a unique B-tree index on the listed columns, and that index is what does the work. Everything surprising about unique constraints follows from that one fact.

The first consequence is locking. ALTER TABLE ... ADD CONSTRAINT takes an ACCESS EXCLUSIVE lock on the table, which conflicts with every other lock mode — including plain SELECT. Because the index is built inside that statement, the lock is held for the whole build. On a 200 GB table that is a multi-minute outage, and the lock request itself queues behind any long-running transaction, blocking everything that arrives afterwards.

The second is NULLs. By default a unique constraint treats nulls as distinct values, so a column marked UNIQUE will happily accept a thousand NULL rows. PostgreSQL 15 added NULLS NOT DISTINCT to change that, but the default is unchanged and matches the SQL standard.

The third is redundancy. Because the constraint owns an index, creating your own unique index over the same columns and adding a constraint leaves two identical B-trees: double the write amplification, double the bloat, one of them never chosen by the planner. This is the failure mode behind the duplicate-index finding, and it is common — across 31 monitored instances in our fleet, 67.7% fail that check. Related shape problems are covered in Postgres schema mistakes.

The fourth is that a failed CREATE UNIQUE INDEX CONCURRENTLY does not clean up after itself. The docs warn that if the build fails, an invalid index is left behind; it is ignored for queries but still maintained on every insert and update. A duplicate-key violation partway through a concurrent build is the usual cause, which is why you check for duplicates first.

How to detect it

Before adding the constraint, find the rows that would reject it:

SELECT email, count(*) AS duplicate_row_count
FROM   users
GROUP  BY email
HAVING count(*) > 1
ORDER  BY duplicate_row_count DESC
LIMIT  50;

Zero rows means the constraint will build cleanly. Any rows at all, and the ALTER TABLE will fail after doing all the scan work — so clean up first. To see the actual duplicates with enough context to decide which to keep:

SELECT id, email, created_at,
       row_number() OVER (PARTITION BY email ORDER BY created_at, id) AS keep_rank
FROM   users
WHERE  email IN (SELECT email FROM users GROUP BY email HAVING count(*) > 1)
ORDER  BY email, keep_rank;

To find indexes left invalid by a failed concurrent build, and unique indexes that duplicate a constraint:

-- invalid indexes (a failed CREATE INDEX CONCURRENTLY)
SELECT n.nspname AS schema_name, c.relname AS index_name
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  NOT i.indisvalid;

-- unique indexes with no constraint attached to them
SELECT c.relname AS index_name, t.relname AS table_name
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
JOIN   pg_class t ON t.oid = i.indrelid
WHERE  i.indisunique
AND    NOT EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conindid = i.indexrelid);

The second query is a judgement call, not a defect list: a bare unique index is a perfectly good way to enforce uniqueness. It only matters when a constraint over the same columns also exists.

How MyDBA shows this

The index advisor groups an instance's indexes into cards — Unused Indexes, Duplicate Indexes, Missing Index Opportunities, Bloated Indexes — and a unique index that overlaps a constraint's own index surfaces under Duplicate Indexes with the DROP INDEX CONCURRENTLY statement already written out. The indexes health domain scores the same recommendations continuously as checks F1–F4, and its invalid-index finding is where a failed CREATE UNIQUE INDEX CONCURRENTLY shows up rather than staying silent until someone reads pg_index by hand.

The schema domain covers the other half: check G1 flags tables with no primary key, and across 28 instances scored for schema, 57.1% have at least one. If you want the same view of your own instances, the free PostgreSQL health check scores the indexes and schema domains without any setup beyond a read-only connection.

How to fix it

1. Remove the duplicates. Decide the rule first — oldest wins, most-complete wins, merge — then apply it. A common shape, keeping the earliest row per key:

BEGIN;
DELETE FROM users u
USING (
  SELECT id,
         row_number() OVER (PARTITION BY email ORDER BY created_at, id) AS keep_rank
  FROM   users
) ranked
WHERE u.id = ranked.id AND ranked.keep_rank > 1;
-- re-run the HAVING count(*) > 1 query here; it must return zero rows
COMMIT;

2. Build the index without blocking writes.

CREATE UNIQUE INDEX CONCURRENTLY users_email_uk ON users (email);

This runs outside a transaction block, takes two table scans, and holds only SHARE UPDATE EXCLUSIVE. Check it finished valid before going further:

SELECT indisvalid FROM pg_index WHERE indexrelid = 'users_email_uk'::regclass;

If that returns false, drop it with DROP INDEX CONCURRENTLY users_email_uk;, fix the duplicates that arrived during the build, and retry.

3. Attach the index as the constraint.

ALTER TABLE users
  ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_uk;

This still takes ACCESS EXCLUSIVE, but only for a catalog update — no index build — so it is measured in milliseconds. PostgreSQL renames the index to the constraint name if they differ. The documented restrictions are worth knowing: the index must be a unique, non-partial, non-expression B-tree index with default sort ordering. Set a short lock_timeout so a failed attempt fails fast instead of queueing:

SET lock_timeout = '3s';

4. If the columns are nullable and you want one NULL only, use PostgreSQL 15's NULLS NOT DISTINCT:

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE NULLS NOT DISTINCT (email);

5. If uniqueness applies only to some rows, a constraint cannot express that — a partial unique index can:

CREATE UNIQUE INDEX CONCURRENTLY users_active_email_uk
  ON users (email) WHERE deleted_at IS NULL;

That is the standard escape hatch for soft deletes and one-active-record-per-tenant rules; see partial indexes in PostgreSQL. A partial index cannot be attached with USING INDEX, so it stays an index — which is fine, because ON CONFLICT and foreign keys care about the index, not the catalog label.

How to prevent it

Pick one enforcement mechanism per rule and write it down. If the columns are a real business key that other tables reference, use a constraint: a foreign key requires a unique constraint or unique index on the referenced columns. If the rule is conditional, use a partial unique index and do not add a constraint alongside it. Never do both over identical columns — that is exactly the duplicate index the advisor reports.

Order multi-column constraints by how you query them. UNIQUE (tenant_id, email) and UNIQUE (email, tenant_id) reject exactly the same rows, but only the first gives you a usable index for WHERE tenant_id = $1, because a B-tree can be scanned on a leading subset of its columns. Put the column you filter on alone first.

Two rules that are easy to forget. On a partitioned table, the unique constraint must include all of the partition key columns — there is no global unique index across partitions, so a UNIQUE (email) on a table partitioned by created_at is simply rejected. And INSERT ... ON CONFLICT needs a unique index or constraint to infer an arbiter from; if the inference specification does not match one, the statement errors rather than silently falling back.

Use DEFERRABLE INITIALLY DEFERRED sparingly, for the case it was designed for: reshuffling values within one transaction, such as swapping two rows' positions, where an intermediate state would collide. The cost is real — deferred checks accumulate in memory until commit, and a deferrable constraint cannot arbitrate ON CONFLICT DO UPDATE.

Finally, monitor for invalid indexes rather than trusting that a concurrent build succeeded. A migration that ran overnight and failed at 99% leaves a maintained, unused index behind, and nothing will tell you. That check belongs in the same place as the rest of your PostgreSQL health check checklist, alongside decisions like UUID versus bigint primary keys.

FAQ

What is the difference between a unique constraint and a unique index?

Functionally very little: the constraint creates and owns a unique index, and both reject duplicates identically. The differences are cataloguing and capability. A constraint appears in pg_constraint and information_schema, can be referenced by a foreign key, and can be DEFERRABLE. An index can be partial or built on expressions, which a constraint cannot. Prefer the constraint when it can express the rule.

Can I add a unique constraint without locking the table?

Not entirely, but you can reduce the lock to milliseconds. Build the index with CREATE UNIQUE INDEX CONCURRENTLY, which holds only SHARE UPDATE EXCLUSIVE and lets reads and writes continue, then attach it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX. The attach step still takes ACCESS EXCLUSIVE, but does no scanning. Set lock_timeout so a blocked attach fails rather than queueing behind a long transaction.

Why does my unique column allow multiple NULLs?

Because the SQL standard says two nulls are not equal, and PostgreSQL follows it: nulls are treated as distinct in a unique constraint. If you want at most one NULL, either make the column NOT NULL, or use UNIQUE NULLS NOT DISTINCT (col) on PostgreSQL 15 or later.

Does ON CONFLICT work with a partial unique index?

Yes, but you must repeat the index's WHERE clause in the inference specification: INSERT ... ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE .... Without it, PostgreSQL cannot match an arbiter index and raises an error. A deferrable unique constraint cannot be used as an arbiter at all.

How do I clean up after a failed CREATE UNIQUE INDEX CONCURRENTLY?

Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, then run DROP INDEX CONCURRENTLY <name>;. Do not leave it in place: an invalid index is never used by the planner but is still updated by every write. Then fix the duplicate rows that caused the failure and rebuild.

Part of the The PostgreSQL health check checklist guide. Last verified against PostgreSQL 18, 2026-09-17.