How do you create a unique index in PostgreSQL?

Run CREATE UNIQUE INDEX idx_users_email ON users (email);. PostgreSQL builds a B-tree and rejects any insert or update that would duplicate an existing key. The index is the enforcement mechanism a UNIQUE constraint uses too — but only an index can carry a WHERE clause, which is the main reason to create one directly.

Key facts

| Fact | Value | |---|---| | Statement | CREATE UNIQUE INDEX [CONCURRENTLY] name ON table (cols) [WHERE pred] (CREATE INDEX) | | Access methods supporting UNIQUE | B-tree only (CREATE INDEX) | | NULL handling | Multiple NULLs allowed by default; nulls are not considered equal (Unique Indexes) | | Opt out of that | NULLS NOT DISTINCT, PostgreSQL 15+ (Unique Indexes) | | Adding a UNIQUE constraint | Automatically creates a unique B-tree index behind it (Unique Constraints) | | Foreign key targets | Must be a primary key or a unique constraint — an index alone is not enough (Foreign Keys) | | Failed CONCURRENTLY build | Leaves an invalid index that must be dropped by hand (CREATE INDEX) | | Duplicate indexes across our fleet | 67.7% of 31 monitored instances fail F3_duplicate_indexes | | Tables with no primary key | 57.1% of 28 monitored instances fail G1_no_primary_key |

Why this happens

A unique index is an ordinary B-tree with one extra rule: before a new entry is written, PostgreSQL checks whether an equal key already exists and raises 23505 unique_violation if it does. That check happens at index-insert time, inside the same transaction as the write, which is why uniqueness in PostgreSQL is always backed by an index and never by a separate lookup table.

CREATE UNIQUE INDEX idx_users_email ON users (email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');
-- ERROR:  duplicate key value violates unique constraint "idx_users_email"
-- DETAIL:  Key (email)=(ada@example.com) already exists.

Only B-tree supports UNIQUE; hash, GIN, GiST, SP-GiST and BRIN do not (CREATE INDEX). Everything else in the CREATE INDEX syntax — INCLUDE, WHERE, expressions, CONCURRENTLY — is still available.

Index or constraint?

Writing ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email) creates the same unique B-tree underneath (Unique Constraints). The data-integrity guarantee is identical. Three differences matter in practice:

1. Only a constraint appears in information_schema.table_constraints. Tools that diff schemas, generate ORM models or document the database read that view. A bare unique index is invisible to them, which is a real source of "the staging schema doesn't match production" reports that turn out to be a naming difference rather than a missing rule. 2. Only a constraint can be the target of a foreign key. This is the point people get wrong most often. REFERENCES users (email) requires a primary key or a unique constraint on email; a unique index with identical columns is rejected with there is no unique constraint matching given keys (Foreign Keys). If anything will ever reference those columns, declare a constraint. 3. Only an index can be partial or expression-based. A constraint's column list is a plain list of columns, with no WHERE and no function calls. That restriction is the whole reason CREATE UNIQUE INDEX remains the more useful tool.

The constraint side — ADD CONSTRAINT ... UNIQUE, promoting an existing index with USING INDEX, NULLS NOT DISTINCT, ON CONFLICT, DEFERRABLE, and the partitioned-table rules — is covered in detail in the unique constraint guide. The rest of this article is about the index.

Partial unique indexes: the soft-delete case

This is the pattern worth memorising. A table with soft deletes cannot use a plain unique constraint on email, because a deleted row keeps occupying the value and the same person can never sign up again. A partial index applies the rule only to live rows (Partial Indexes):

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

Now two soft-deleted rows may share an address, a live row and a deleted row may share one, and two live rows may not. There is no constraint syntax that expresses this.

The same shape solves "exactly one default per owner", where a plain constraint on (owner_id, is_primary) would wrongly allow only one non-default row per owner:

CREATE UNIQUE INDEX idx_addresses_one_primary
    ON addresses (owner_id)
    WHERE is_primary;

Both indexes are also smaller and cheaper to maintain than their full-table equivalents, because only matching rows get an entry.

Expression unique indexes: case-insensitive email

Email addresses are effectively case-insensitive, but text comparison is not. Index the normalised form instead (Expression Indexes):

CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

ada@example.com and Ada@Example.com now collide. The cost: the expression is evaluated on every insert and update of that column, and a query only uses the index if it spells the expression the same way — WHERE lower(email) = lower($1), not WHERE email = $1.

The alternative is the citext type, which compares case-insensitively by itself so a plain unique index or constraint works and ordinary WHERE email = $1 queries match (citext). Its trade-offs: it is an extension, so it must be installed in every database and survive every restore; it is not the type your ORM picks by default; and its case folding is locale-dependent in ways that surprise people with non-ASCII data. For a single column, lower() with an expression index is usually the less invasive choice. For a schema where dozens of columns need the behaviour, citext pays for itself.

The NULL rule

By default, NULL values in a unique index are not considered equal to each other, so any number of rows may carry a NULL key (Unique Indexes). In a multicolumn unique index the rule is per row, not per column: a row conflicts only if every key column is equal, and a NULL in any one of them means no conflict — so (1, NULL) can be inserted repeatedly.

CREATE UNIQUE INDEX idx_t_ab ON t (a, b);
INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (1, NULL);   -- succeeds; NULL is not equal to NULL

If that is not what you want, PostgreSQL 15 and later accept CREATE UNIQUE INDEX ... ON t (a, b) NULLS NOT DISTINCT, which treats NULLs as equal.

What the planner does with it

Uniqueness is a proof, not just a rule. When a query filters on every column of a unique index, the planner knows at most one row can match, so the estimate collapses to rows=1 and it stops considering plans that make sense only for many rows — no bitmap scan, no hash aggregate to deduplicate, and a nested loop over that side becomes cheap and predictable.

EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';
--  Index Scan using idx_users_email on users  (cost=0.28..8.30 rows=1 width=72)
--    Index Cond: (email = 'ada@example.com'::text)

Two knock-on effects you can observe with EXPLAIN: a DISTINCT or GROUP BY over a unique key needs no deduplication step, and a LEFT JOIN to a table whose join column is unique — and whose columns are not otherwise used — can be removed from the plan entirely, because uniqueness proves the join cannot change the row count. Removing a unique index that looks "unused" by scan count can therefore change plans elsewhere.

How to detect it

Before you build a unique index, find the rows that would break it. Otherwise the build fails after doing all its work.

-- duplicates on a plain key
SELECT email, count(*) AS copies, min(id) AS keep_id
FROM users
GROUP BY email
HAVING count(*) > 1
ORDER BY copies DESC;

-- duplicates under a partial predicate (only the rows the index will cover)
SELECT email, count(*)
FROM users
WHERE deleted_at IS NULL
GROUP BY email HAVING count(*) > 1;

-- duplicates under the expression you intend to index
SELECT lower(email), count(*)
FROM users
GROUP BY 1 HAVING count(*) > 1;

An empty result means the build will succeed — as long as nothing writes a duplicate between the check and the build.

To see which unique indexes exist, which are backed by a real constraint, and which are invalid after a failed build:

SELECT i.indexrelid::regclass  AS index_name,
       i.indrelid::regclass    AS table_name,
       i.indisunique,
       i.indisvalid,                       -- false = failed CONCURRENTLY build
       c.conname                           -- NULL = index only, no constraint
FROM pg_index i
LEFT JOIN pg_constraint c ON c.conindid = i.indexrelid
WHERE i.indisunique
  AND i.indrelid::regclass::text NOT LIKE 'pg\_%'
ORDER BY i.indisvalid, table_name;

Rows with indisvalid = false are enforcing nothing and still costing write throughput. Rows with conname IS NULL are indexes without a constraint — fine unless a foreign key needs to point at them.

How MyDBA shows this

Duplicate indexes: pairs whose column lists overlap, with the redundant one and the space it wastes called out.

MyDBA's Index Advisor groups its findings into cards — Unused Indexes, Missing Index Opportunities, Duplicate Indexes ("potentially redundant indexes"), Low Usage Indexes, Bloated Indexes — and the duplicate card is where unique indexes usually surface, because a unique index plus a separate plain B-tree on the same leading columns is a classic redundancy: the unique index already serves every lookup the plain one does, so the plain one is pure write overhead and disk. Across 31 monitored instances, 67.7% currently fail the F3_duplicate_indexes health check. The schema domain's G1_no_primary_key check covers the opposite problem — a table with no unique key at all, which 57.1% of 28 monitored instances have somewhere. If you want to see which of your own tables carry redundant indexes and which have no primary key, start with the free PostgreSQL health check.

How to fix it

1. Find the duplicates with the GROUP BY ... HAVING queries above, using the exact predicate and expression the index will use. 2. Resolve them — merge, delete or soft-delete the losing rows. Keep the row you decided on explicitly (min(id), max(created_at)), not whichever one the database returns first. 3. Build without blocking writes. On anything but a small table, use CONCURRENTLY: ``sql CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_live ON users (email) WHERE deleted_at IS NULL; ` It cannot run inside a transaction block, it makes two passes over the table, and it is slower than a blocking build by design. The concurrent build guide covers the mechanics. 4. Check that it succeeded. If a duplicate slipped in while the build was running, the statement fails and leaves an invalid index behind — it is not rolled away: `sql SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; DROP INDEX CONCURRENTLY idx_users_email_live; -- then fix the data and retry ` An invalid unique index enforces nothing but is still maintained on every write, so leaving one in place is the worst of both outcomes. 5. Promote it to a constraint if anything will reference it. ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX idx_users_email;` reuses the index you just built rather than building a second one — see the unique constraint guide. This does not work for partial or expression indexes, which can never become constraints.

How to prevent it

Decide index-or-constraint once, per table, and write it down: if the rule is plain columns and something may reference it, use a constraint; if it needs WHERE or a function, it has to be an index. Add the unique index in the same migration that introduces the column, while the table is small and a blocking build costs milliseconds. Check for redundancy whenever you add one — a new unique index frequently makes an older plain index on the same leading columns dead, and that index will not remove itself. Alert on NOT indisvalid in pg_index; an invalid index is a silent failure that no application error will ever tell you about. And keep a primary key on every table, which is the same mechanism applied to identity rather than to a business rule.

FAQ

Can I create a unique index on a subset of rows?

Yes, and it is the main reason to prefer an index over a constraint. CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL enforces uniqueness only among live rows (Partial Indexes). A UNIQUE constraint has no WHERE clause, so this rule cannot be written as a constraint at all.

Why does my unique index allow duplicate NULLs?

Because nulls are not considered equal to one another, so each NULL key is distinct and any number of them can coexist (Unique Indexes). In a multicolumn index the same applies per row: a NULL in any key column means the row cannot conflict with anything. From PostgreSQL 15, add NULLS NOT DISTINCT to treat NULLs as equal.

Does a unique index make queries faster?

It is a B-tree, so it speeds up lookups exactly like a non-unique index on the same columns. The extra benefit is planning: an equality filter on the full key lets the planner prove at most one row matches, which shows up as rows=1 in EXPLAIN and removes the need for deduplication steps above it. The extra cost is a uniqueness check on every insert and update of those columns.

What happens if CREATE UNIQUE INDEX CONCURRENTLY finds duplicates?

The statement fails, and the invalid index it built stays in the catalog. It enforces nothing, may be skipped by the planner, and is still updated by every write. Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid, drop it with DROP INDEX CONCURRENTLY, clean the duplicate rows, and run the build again (CREATE INDEX).

Can a foreign key reference a unique index?

No — it must reference a primary key or a unique constraint (Foreign Keys). A unique index with the right columns is rejected with there is no unique constraint matching given keys. If an index already exists, promote it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX, which adopts the index instead of building a duplicate one.

Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.