Incident Response

PostgreSQL "duplicate key value violates unique constraint": Causes and Fixes

The error looks like a data problem, but on a primary key it is usually a sequence that fell behind after a restore, a migration or a failover. Here is how to tell which case you have, fix the sequence safely, and stop race conditions and duplicates on large tables.

· 14 min read · AG Data Team

"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.

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:

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;

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

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:

Key takeaways

PostgreSQL Sequences Errors Upsert
Work with us

Inserts failing on duplicate keys in production?

We find out why sequences and data drifted apart, fix them without downtime and check that a migration or failover did not leave other tables in the same state.

Get in touch More from the blog