Duplicate Key Creating Unique Index With Duplicate Rows
CREATE UNIQUE INDEX, ALTER TABLE ... ADD CONSTRAINT ... UNIQUE, or ALTER TABLE ... ADD PRIMARY KEY fails with SQLSTATE 23505 and the message "could not...
In 10 seconds
A migration finally adds the uniqueness that users.email should have had from the start: CREATE UNIQUE INDEX users_email_email_key ON users_email(email). It passes in dev and staging, then fails the moment it runs in production with ERROR: 23505: could not create unique index "users_email_email_key" and DETAIL: Key (email)=([email protected]) is duplicated. While no constraint was enforcing uniqueness, duplicate rows crept in -- the same email signed up more than once, an import ran twice, a backfill double-wrote. CREATE UNIQUE INDEX sorts the column to check uniqueness and aborts on the first pair of equal keys it meets. This is the dedup sibling of adding a foreign key to a table full of orphans: same shape (a constraint meeting dirty data), different dirt and a different cleanup -- here you keep one row per key and delete the rest.
Why This Happens
A unique index is a promise that no two live rows share the indexed value. To create one, PostgreSQL sorts the column's values and walks them in order; when it finds two adjacent equal keys it raises unique_violation (SQLSTATE 23505) and rolls the whole build back -- the index is never created. The DETAIL line names just ONE duplicated value (whichever the sort happened to land on), which is why fixing that single value and re-running simply surfaces another.
The duplicates exist precisely because there was no unique constraint while those rows were written: an application without a uniqueness check, a retried import, or a backfill that ran twice.
Once the index does exist it enforces the promise both ways -- it blocks the build until the data is clean, and afterward it rejects any new write that would create a duplicate. Once the index does exist it enforces the promise both ways -- it blocks the build until the data is clean, and afterward it rejects any new write that would create a duplicate.
Diagnose
Free Tier -- Basic Approach
The error names only one duplicated value. A single GROUP BY ... HAVING count(*) > 1 lists every email that is duplicated and how many copies each has, so you know the full scope before touching the data.
SELECT email, count(*) AS copies FROM users_email GROUP BY email HAVING count(*) > 1 ORDER BY email;
email | copies -------------------+-------- [email protected] | 3 [email protected] | 2 [email protected] | 2 (3 rows)
Group the column the index failed on and keep groups bigger than one. Here it returns three rows: [email protected] (3 copies), [email protected] (2), [email protected] (2).
Advanced Approach -- Production-Safe
The free list shows which values collide; the premium report shows which exact ROWS to keep versus delete (lowest id wins) and how many rows must go per key, turning the dedup into a reviewed plan rather than a blind DELETE.
SELECT id, email, row_number() OVER (PARTITION BY email ORDER BY id) AS rn FROM users_email WHERE email IN (SELECT email FROM users_email GROUP BY email HAVING count(*) > 1) ORDER BY email, id;
id | email | rn
------+-------------------+----
1 | [email protected] | 1
1001 | [email protected] | 2
1002 | [email protected] | 3
2 | [email protected] | 1
1003 | [email protected] | 2
3 | [email protected] | 1
1004 | [email protected] | 2
(7 rows)
SELECT email, count(*) - 1 AS rows_to_delete FROM users_email GROUP BY email HAVING count(*) > 1 ORDER BY email;
email | rows_to_delete -------------------+---------------- [email protected] | 2 [email protected] | 1 [email protected] | 1 (3 rows)
Part 1 ranks every duplicate row with row_number OVER (PARTITION BY email ORDER BY id): rn = 1 is the survivor (ids 1, 2, 3), the higher rn rows (ids 1001..1004) are the ones to delete. Part 2 shows rows_to_delete per email (user1 -> 2, user2 -> 1, user3 -> 1), so you can confirm the cleanup size before running it.
Fix
Keep one row per key (the lowest id) and delete the duplicates with a self-join, then the CREATE UNIQUE INDEX that failed now builds cleanly -- and the index immediately rejects a duplicate write.
DELETE FROM users_email a USING users_email b WHERE a.email = b.email AND a.id > b.id;
DELETE 4
CREATE UNIQUE INDEX users_email_email_key ON users_email(email);
CREATE INDEX
INSERT INTO users_email(email) VALUES ('[email protected]');
ERROR: duplicate key value violates unique constraint "users_email_email_key" DETAIL: Key (email)=([email protected]) already exists.
DELETE FROM users_email a USING users_email b WHERE a.email = b.email AND a.id > b.id removes the 4 duplicate rows (DELETE 4), keeping the lowest id per email. CREATE UNIQUE INDEX users_email_email_key ON users_email(email) now returns CREATE INDEX.
A duplicate INSERT ([email protected]) is rejected with 23505 -- proof the index is enforcing uniqueness.
Verify
Healthy state is three facts together: zero duplicate groups remain, the unique index exists, and a fresh duplicate write is rejected -- the data is deduplicated and uniqueness is enforced going forward.
SELECT count(*) AS duplicate_emails FROM (SELECT email FROM users_email GROUP BY email HAVING count(*) > 1) d;
duplicate_emails
------------------
0
(1 row)
SELECT indexname FROM pg_indexes WHERE tablename = 'users_email' AND indexname = 'users_email_email_key';
indexname ----------------------- users_email_email_key (1 row)
INSERT INTO users_email(email) VALUES ('[email protected]');
ERROR: duplicate key value violates unique constraint "users_email_email_key" DETAIL: Key (email)=([email protected]) already exists.
The duplicate-group audit returns duplicate_emails = 0. pg_indexes shows users_email_email_key on users_email. A fresh duplicate INSERT ([email protected]) is rejected with duplicate key value violates unique constraint, confirming enforcement.
⏪ Rollback
Nothing to roll back from the failed CREATE UNIQUE INDEX itself: because the build aborts, no index is created and the table is untouched. The remediation does change data -- the DELETE removes duplicate rows. Decide deliberately which copy to keep: keeping the lowest id (the earliest row) is the safe default, but if a later duplicate holds the real data (a corrected name, a newer profile), keep that one instead -- partition by the key and order by whatever column makes the surviving row correct. If the 'duplicates' are not truly identical entities (two different people who happen to share an email), the answer may be to change the data model, not delete rows. Run the audit first so you see exactly which rows are involved before deleting anything.
📦 Version Matrix (PG 14--18)
Same mechanism on PostgreSQL 14, 15, 16, 17 and 18: with duplicates present CREATE UNIQUE INDEX fails with "could not create unique index"; after the duplicates are removed the identical build succeeds (CREATE INDEX).
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=could not create unique index | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=could not create unique index | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=could not create unique index | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=could not create unique index | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=could not create unique index | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
The error named only [email protected]. How do I find all the duplicates?
Group by the key and keep the groups bigger than one: SELECT email, count(*) FROM users_email GROUP BY email HAVING count(*) > 1. The CREATE UNIQUE INDEX error stops at the first duplicate the sort reaches, but this query returns every duplicated value. In the lab there are three (user1 x3, user2 x2, user3 x2).
How do I delete duplicates but keep exactly one row per value?
Pick a deterministic survivor. The self-join DELETE FROM users_email a USING users_email b WHERE a.email = b.email AND a.id > b.id keeps the lowest id per email and removes the rest. Equivalently, rank with row_number() OVER (PARTITION BY email ORDER BY id) and delete every row where rn > 1. Change the ORDER BY if a different copy should win.
Should I CREATE UNIQUE INDEX or ADD CONSTRAINT UNIQUE?
They produce the same enforcement; ALTER TABLE ... ADD CONSTRAINT ... UNIQUE builds a unique index under the hood and records a named constraint in the catalog, while a bare CREATE UNIQUE INDEX gives you the index (and the CONCURRENTLY option for online builds on busy tables). Both fail with 23505 on duplicate data, so the cleanup step is identical.
Can I build the index without blocking writes?
Yes, once the data is clean: CREATE UNIQUE INDEX CONCURRENTLY does not take a write-blocking lock. It cannot run inside a transaction block, takes longer, and if it meets a duplicate it leaves an INVALID index you must DROP and retry -- so deduplicate first, then build concurrently.
How do I keep duplicates from coming back?
Two things: keep the unique index in place so the database rejects new duplicates, and add a standing guard that counts duplicate groups -- SELECT count(*) FROM (SELECT email FROM users_email GROUP BY email HAVING count(*) > 1) d -- which should always return 0. Wire it into CI so any path that reintroduces duplicates is caught.