PostgreSQL

PostgreSQL — ERROR: duplicate key value violates unique constraint

Written and reviewed by Sahil Srivastav

PostgreSQLConstraintsIdempotency
ERROR:  duplicate key value violates unique constraint "orders_idempotency_key_key"
DETAIL:  Key (idempotency_key)=(a3f9c1e0-77b2-4d9a-9f31-2c8e5b1d4a60) already exists.

What this error actually means

A unique index rejected a row because an equal key already exists. The `DETAIL` line gives you the constraint name and the exact conflicting value, which is unusually helpful — most of the diagnostic work is already done for you.

The interesting question is not how to make the error stop. It is whether the error is *correct*. Very often it is: the constraint is the last line of defence against a duplicate charge, a double-booked seat, or a repeated webhook, and it fired because something upstream tried to do the operation twice. Suppressing it would convert a safe failure into silent data corruption.

The most common genuine bug behind it is check-then-insert. Code that runs `SELECT` to see whether a key exists and then `INSERT` if it does not has a window between the two statements. Two concurrent requests both see nothing and both insert; one gets this error. The `SELECT` provides no protection whatsoever under concurrency — the unique index is what actually enforces the invariant.

Causes, most common first

  1. 1Check-then-insert race. Read to see whether the key exists, then insert. Two concurrent executions both pass the check. The read is not a lock and grants no exclusivity, so this pattern is broken under any concurrency at all.
  2. 2A retry doing the work a second time. The first attempt succeeded but the response was lost — a timeout, a dropped connection, a broker redelivery. The retry re-inserts. Here the constraint is working exactly as intended and the fix is to make the operation idempotent, not to remove the constraint.
  3. 3At-least-once delivery without idempotent consumption. Message brokers and webhook senders redeliver by design. A consumer that inserts unconditionally will eventually hit this. The error is the system telling you the consumer is not idempotent.
  4. 4A sequence out of sync with existing data. After a restore, a bulk load with explicit ids, or a botched migration, the sequence backing a serial column is behind the maximum existing id, so generated ids collide with real rows until it catches up.
  5. 5A genuinely wrong uniqueness assumption. The constraint encodes a rule that does not hold in reality — an email that must be unique globally but is legitimately shared by a shared mailbox, or a natural key that is only unique per tenant. Here the schema is the bug, not the code.

When you see it

  • Occurs only under concurrency, and disappears in single-threaded testing
  • Correlates with client retries, webhook redelivery, or a user double-clicking submit
  • The conflicting value in `DETAIL` is an idempotency key, external id, or natural key
  • Rate rises after adding a retry policy upstream — the retries are now colliding by design
  • In batch loads it appears mid-run and aborts the remainder of the transaction

How to diagnose it

Step 1

Read the constraint definition before anything else

The constraint name from the error tells you which columns are involved. Confirm the definition rather than assuming, especially for partial or expression indexes where the uniqueness scope is not obvious.

SELECT indexdef FROM pg_indexes WHERE indexname = 'orders_idempotency_key_key';

Step 2

Look at the existing row

Fetch the row holding the conflicting value. If it is identical to what you are inserting, this is a duplicate delivery or retry. If it differs, you have a genuine key collision and a modelling problem.

SELECT * FROM orders WHERE idempotency_key = 'a3f9c1e0-77b2-4d9a-9f31-2c8e5b1d4a60';

Step 3

Check whether a check-then-insert exists in the path

Search the code path for a `SELECT` on the same key followed by an `INSERT`. If you find one, you have found the bug regardless of what else is true.

Step 4

For serial columns, compare sequence to max id

A sequence behind the table maximum produces collisions on every insert until it catches up. This is a five-second check that saves hours.

SELECT last_value FROM orders_id_seq;
SELECT max(id) FROM orders;

The fix

Replace check-then-insert with a single atomic statement. `INSERT ... ON CONFLICT (key) DO NOTHING RETURNING *` inserts if absent and tells you which happened, in one round trip, with no race window. Use `DO UPDATE` when the second arrival should refresh the row. This is the correct fix for the large majority of cases and it removes the race rather than handling it.

For retries and redelivery, make the operation idempotent around a key the caller supplies. Persist the key with a unique index — then a duplicate arrival collides, you detect it, and you return the original result instead of performing the work again. Treating this as "handle the duplicate-key error" rather than "prevent the duplicate work" is the distinction between a system that double-charges and one that does not.

Never suppress the error to make the symptom go away. Catching `unique_violation` and continuing is only correct when you have established that the existing row is the outcome you wanted; otherwise you are discarding a write and reporting success.

For an out-of-sync sequence, reset it to the table maximum: `SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));` — and find out how explicit ids came to be inserted, because it will happen again.

If the uniqueness rule itself is wrong, change the schema deliberately. A partial unique index (`WHERE deleted_at IS NULL`) or a composite key including tenant is usually what was actually meant.

-- Race window between the two statements: both callers pass the check
SELECT 1 FROM orders WHERE idempotency_key = $1;
INSERT INTO orders (idempotency_key, ...) VALUES ($1, ...);

-- Atomic, no window, and it tells you which branch happened
INSERT INTO orders (idempotency_key, amount, status)
VALUES ($1, $2, 'PENDING')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;
-- 0 rows returned => a concurrent or earlier request already created it;
-- fetch and return that one rather than doing the work again.

How to stop it coming back

  • Treat every unique index as a real invariant with a real error path, not as a safety net you hope never fires
  • Require an idempotency key on every mutating endpoint that a client may retry, and persist it under a unique index
  • Ban check-then-insert in review; `ON CONFLICT` is shorter and correct
  • After any restore or bulk load with explicit ids, reset the affected sequences as a standard step
  • Alarm on unique-violation rate — a rising rate usually means an upstream retry policy changed

Practise this failure in a real repository

Gronex ships this as a runnable repository: a webhook consumer facing duplicate and out-of-order deliveries. The tests redeliver events and replay them in the wrong order, asserting the resulting state is correct — so catching the duplicate-key error is not enough to pass.

FAQ

Should I catch the error or use ON CONFLICT?

`ON CONFLICT` for anything you can express, because it is one statement with no race window and no aborted transaction to recover from. Catching the violation is a valid fallback when the conflict target is not known in advance, but remember the transaction is aborted afterwards — you need a savepoint if you intend to continue.

Is this error ever a good thing?

Frequently. When it stops a duplicate webhook from charging a card twice or a retry from double-booking a seat, the constraint just prevented a correctness incident. The right response is to make the caller idempotent, not to remove the constraint.

Why does the insert still fail inside a transaction with a preceding check?

Because `READ COMMITTED` snapshots do not see the other transaction’s uncommitted insert, and the unique index is enforced at write time. The check and the insert are separate events with a gap between them, and concurrency exploits it. Only the index enforces uniqueness.

Does ON CONFLICT DO NOTHING tell me whether it inserted?

Only via `RETURNING`: rows come back if the insert happened and nothing comes back if it conflicted. That distinction is usually the whole point, so always include `RETURNING` and branch on it rather than assuming success.

Related

Current transaction is abortedTransactional outbox implementation

Other errors engineers hit next to this one

Full error and symptom index →