"duplicate key value violates unique constraint" is PostgreSQL refusing an INSERT or UPDATE that would create a second row with the same value in a primary key or unique column. The error itself is simple. What matters is why the duplicate value appeared, and on a primary key the answer is usually not the application, but a sequence that is behind the data.
Reading the error
ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1042) already exists.
- The constraint name tells you which index fired.
*_pkeyis the primary key,*_keyis usually a unique constraint created withUNIQUE, anything else is a unique index someone named by hand. - The
DETAILline shows the exact key that already exists. - The SQLSTATE is
23505,unique_violation(PostgreSQL error codes). Catch that code in the application, not the message text, which can be translated.
Every unique constraint and primary key is enforced by a unique index that PostgreSQL creates automatically (unique indexes). So the question is always the same: which value collided, and where did it come from?
Case 1: the sequence is behind the data
This is the classic. The table has rows up to id = 50000, but the sequence will hand out 1042 next. Every insert that relies on the default collides with an existing row until the sequence moves past the existing values. Typical ways to get there:
- Data was loaded with explicit ids (
COPY, an ETL tool, a hand-written migration script) and nobody moved the sequence afterwards. - Rows were copied from another database, for example during a MySQL-to-PostgreSQL move, while the sequence stayed at its start value.
- An identity column was filled with
OVERRIDING SYSTEM VALUE. The documentation is clear that this makes supplied values override the sequence (INSERT); the sequence itself does not advance. - A failover or switchover to a logical replication subscriber. On PostgreSQL 18 and earlier, sequence data is not replicated at all: the rows arrive, but the subscriber's sequence still shows its start value (logical replication restrictions). We cover this in PostgreSQL logical replication.
Check it in ten seconds:
-- which sequence feeds the column (works for serial and identity)
SELECT pg_get_serial_sequence('public.orders', 'id');
-- where the sequence is vs. where the data is
SELECT last_value, is_called FROM public.orders_id_seq;
SELECT max(id) FROM public.orders;
pg_get_serial_sequence returns the sequence behind a column for both serial/bigserial and identity columns (system information functions), so you do not have to guess its name. If last_value is below max(id), you have found the problem.
Fixing the sequence
-- next nextval() returns max(id)+1 (or 1 on an empty table)
SELECT setval(pg_get_serial_sequence('public.orders', 'id'),
COALESCE(max(id), 0) + 1, false)
FROM public.orders;
With the third argument false, the next nextval returns exactly the value you set; with the two-argument form the sequence advances first (sequence functions). Two properties of setval matter on production: the change is visible to other sessions immediately, and it is not undone if your transaction rolls back. There is no "try it in a transaction and roll back".
After a restore or migration, assume more than one table is affected. This psql snippet generates and runs a fix for every serial and identity column:
-- psql: generate and run setval for every serial/identity column
SELECT format('SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I.%I), 0) + 1, false);',
pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name),
column_name, table_schema, table_name)
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) IS NOT NULL
\gexec
Run it while the application is not writing, or be prepared for a few more errors during the run: rows inserted between the max() and the setval can still collide. If one sequence feeds several tables, in our experience it is safer to set it from the highest value across all of them by hand.
Case 2: a real duplicate from the application
If the sequence is fine, the duplicate came from data: a retried request, a double-click, two workers processing the same message, or a "check if it exists, then insert" pattern. That pattern is a race: two sessions both see "no row" and both insert, and the second one gets the error. The unique constraint is doing its job here. The fix is in the write, not in the database:
INSERT INTO users (email, name)
VALUES ('a@example.com', 'Anna')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
-- or simply skip rows that already exist
INSERT INTO events (event_id, payload)
VALUES (...)
ON CONFLICT (event_id) DO NOTHING;
ON CONFLICT DO UPDATEguarantees an atomic insert-or-update outcome, even under high concurrency (INSERT ... ON CONFLICT).DO UPDATEneeds a conflict target;DO NOTHINGcan omit it and then handles conflicts with all usable unique constraints.- Only
NOT DEFERRABLEconstraints and unique indexes can serve as arbiters, and exclusion constraints are not supported as arbiters forDO UPDATE. - A multi-row upsert that touches the same existing row twice fails with a cardinality violation. Deduplicate the batch before sending it.
For idempotent message processing, a unique key on the event id plus DO NOTHING is usually the cleanest design.
Gaps are normal; collisions are not. A value obtained with nextval is not reused when the transaction aborts (documentation), so failed inserts and upserts leave gaps. Never "fix" gaps by moving a sequence backwards: that is exactly how you create Case 1.
Case 3: NULLs, expressions and partitions
- By default NULLs are not considered equal, so a unique column accepts many NULLs, and a multi-column unique index only rejects rows where all columns are equal.
NULLS NOT DISTINCTchanges that (unique indexes). Teams moving from other databases are often surprised in both directions. - On a partitioned table, a unique or primary key constraint must include all partition key columns, because each partition's index only enforces uniqueness inside that partition (partitioning limitations). A "globally unique" id that is not part of the key is not enforced across partitions.
Adding a unique constraint to a large table
Sometimes the error is the goal: you want the database to reject duplicates that the application has been creating for years. On a big table, do it in two steps:
-- find duplicates before adding a unique constraint
SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 50;
-- then build the index without blocking writes
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
CREATE INDEX CONCURRENTLY does not block writes, but it cannot run in a transaction block. If it hits a duplicate, it fails and leaves an INVALID index behind. The documentation warns that such an index still costs update overhead and, for a unique index, keeps enforcing uniqueness (CREATE INDEX). Drop it and retry after cleaning up, or rebuild it with REINDEX INDEX CONCURRENTLY, see REINDEX CONCURRENTLY. Once the index is valid, ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX turns it into a constraint; for UNIQUE this is a fast operation, though it is not supported on partitioned tables (ALTER TABLE).
What changes at scale
A 10,000-row test database hides most of the real problems:
- The duplicate search is a full scan and a sort. On a table with hundreds of millions of rows, the
GROUP BY ... HAVINGquery can run for a long time and spill to disk. Run it on a replica, or per id range, and expect the cleanup itself to be a batched job, not oneDELETE. - Duplicates keep arriving while you clean. In our experience the reliable order is: fix the write path (upsert or a unique key on the business id) first, then clean history, then build the index. Otherwise the concurrent build fails on rows inserted during the cleanup.
- The index build takes time and disk. A concurrent build scans the table twice and waits for older transactions. Plan for hours on very large tables and for the extra disk space of the new index.
max(id)is cheap, the generator query is not always. On an indexed primary key,max()is fast. On columns without an index, or across hundreds of tables, the "fix all sequences" script scans; run it in a maintenance window.- Failovers to logical replicas. Starting with PostgreSQL 19, logical replication can synchronize sequences, and
ALTER SUBSCRIPTION ... REFRESH SEQUENCESre-syncs them, but only when the publisher also runs version 19 or later (ALTER SUBSCRIPTION, version 19+ docs). On older publishers, setting sequences from the data is part of the switchover runbook. Leave a safe margin abovemax(id)if writes may still be in flight. - Error volume. An application that retries on
23505can generate thousands of errors per second and flood the log. Make sure the retry logic distinguishes a sequence problem (every insert fails) from a real duplicate (one request fails).
Key takeaways
- On a primary key, first compare the sequence with
max(id); a lagging sequence is the most common cause. - Fix it with
setval(pg_get_serial_sequence(...), max + 1, false). It takes effect immediately and cannot be rolled back. - After restores, migrations and logical replication failovers, check every serial and identity column, not just the one that failed.
- For real duplicates, use
ON CONFLICTinstead of check-then-insert. - On large tables, stop new duplicates, clean in batches, then build the unique index
CONCURRENTLYand drop anyINVALIDleftovers.