Skip to main content
SQLSTATE 23505

PostgreSQL unique_violation

An INSERT or UPDATE attempted to create a value combination already protected by a unique constraint or index.

PostgreSQL constraint enforcementData integritywarning
Exact message
ERROR: duplicate key value violates unique constraint

Also searched as: unique_violation · duplicate key

What it means

The database preserved uniqueness and rejected the statement. This may represent an expected race that needs conflict handling, a faulty identifier generator, or application logic that assumed a record did not exist.

Typical trigger conditions

  • Concurrent create requests
  • Replayed messages or retries without idempotency
  • A sequence behind the maximum stored identifier
  • An incorrect natural-key assumption
Evidence before intervention

First diagnostic checks

1

Read the constraint and key detail

Use the named constraint and conflicting values from the server error rather than guessing the affected column.

2

Inspect the constraint definition

Confirm which columns and expressions enforce uniqueness.

SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = '<constraint_name>';
3

Check retry semantics

Decide whether the operation should use ON CONFLICT, return the existing record, or fail visibly.

Version and platform notes

INSERT ... ON CONFLICT is available from PostgreSQL 9.5 onward; conflict behaviour must still match the business operation.

Copyable operational controls

Related engineering templates

The KBY Brief

Follow PostgreSQL failures and fixes

A short email about twice a week with new runbooks, configuration traps and fixes from KBY Technologies.