Skip to content

23505 — unique_violation

PostgreSQL reports 23505 when a row or index operation violates a unique invariant. Diagnose the named object, recover the transaction, and choose a business-safe repair.

At a glance

23505 is PostgreSQL’s unique_violation condition. A row, an index build, or a logical-apply operation found a value that cannot coexist with an enforced unique invariant.

First capture the complete ErrorResponse and the statement that failed. The most useful fields are C (SQLSTATE), M (primary message), D (detail), and, when supplied, s (schema), t (table), and n (constraint or index). In psycopg, these are available through exc.sqlstate and exc.diag. The object fields belong to the wire protocol; they are not standard column names in CSV logs or keys in JSON logs.

The recovery action depends on where the error occurred:

  • An autocommit statement fails, but that connection is ready for its next command.
  • A statement inside an explicit transaction leaves the transaction aborted. Roll back the transaction, or roll back to a savepoint, before issuing another statement. Otherwise PostgreSQL returns 25P02.
  • A deferred unique constraint can accept duplicate rows temporarily and report 23505 at COMMIT.
  • A PL/pgSQL EXCEPTION block can catch the violation in a subtransaction, provided the handler is narrow enough to identify the failing operation.

The selected public runtime records cover 12 distinct passing cases on PostgreSQL 18.6 and 11 passing cases on PostgreSQL 10.21, with NULLS NOT DISTINCT marked not applicable on PG10. The full run remains the source for unreplaced cases; targeted final records select the DML diagnostic, exact log_fields correlation, registry excerpts, and manual transaction boundaries without duplicating or overriding those cases. A separate PG14.24/PG15.19 boundary comparison records unsupported syntax 42601 on PG14 and natural 23505 on PG15 for explicit UNIQUE NULLS NOT DISTINCT; it is excluded from those base counts. The selected run IDs and structured observations are retained in the public evidence JSON; superseded summaries and raw JSONL remain local audit data.

Field Value
SQLSTATE 23505
Condition unique_violation
Status active
Known present by 7.4
Locked snapshots 9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3
Macros ERRCODE_UNIQUE_VIOLATION
Aliases —

The generated locked snapshots above are the catalogue’s release-definition coverage. The executable compatibility targets in this page are PostgreSQL 18.6 and 10.21; the catalogue entry does not claim that 23505 was introduced in PostgreSQL 10.

Meaning and trigger paths

The SQLSTATE directory places 23505 in Class 23, integrity_constraint_violation, with the condition name unique_violation. In the normal btree path, PostgreSQL checks an index entry and raises an error when a conflicting committed or concurrently inserted key is not allowed by the index’s semantics. A primary key is also a unique index, so a duplicate primary-key value uses this condition.

The same SQLSTATE can describe several mechanisms:

  1. DML against a unique constraint or index. An INSERT, or an UPDATE that changes a key, can conflict with a row already protected by a unique index. The normal message template is duplicate key value violates unique constraint "..."; the optional detail is Key (...)=(...) already exists.
  2. A unique index build. Building a unique index over existing duplicate rows uses a different template: could not create unique index "...", with Key (...)=(...) is duplicated. This is an index-build failure, not an ordinary row-insert message.
  3. A deferred constraint. With DEFERRABLE INITIALLY DEFERRED, the duplicate can remain in the transaction until the constraint check at commit. The error is therefore associated with COMMIT, and a failed top-level commit rolls back that transaction.
  4. Logical replication apply. PostgreSQL 18 classifies an apply conflict such as insert_exists; the conflict reporter still maps the relevant insert/update/multiple-unique paths to 23505. The message shape and server log context are different from a client-side btree insert. This page has source and documentation evidence for that path, but the current runtime batch did not create a publisher/subscriber topology.

The code tells you what class of condition occurred. It does not tell you whether the collision is a permanent business duplicate, a race in key selection, or a failed maintenance operation. That distinction comes from the statement, schema, constraint definition, transaction context, and any concurrent activity.

The SQL blocks below are explanatory excerpts with the same operations as the named cases. The authoritative executable source is scripts/verify_cases.py together with verify/cases/23505/cases.json; the excerpts identify their case and omit only disposable schema naming and cleanup, so they are not a second runnable case definition.

Messages and diagnostics

For a normal DML conflict, the PostgreSQL 18.6 source path nbtinsert.c calls BuildIndexValueDescription, then reports the unique violation and attaches the table and constraint identity. A representative run record was:

SQLSTATE: 23505
severity: ERROR
message_primary: duplicate key value violates unique constraint "users_email_key"
message_detail: Key (email)=(a@example.test) already exists.
schema_name: c23505_dml_unique_conflict
table_name: users
constraint_name: users_email_key
source: nbtinsert.c / _bt_check_unique

That detail is conditional. PostgreSQL can omit key values when the caller lacks permission to inspect the relevant columns, when row-level security prevents the description, or for an expression index. The same run with an INSERT-only role kept SQLSTATE and object identity but had no message_detail.

The protocol fields are defined in the Error and Notice Message Fields reference. They should be read from the driver exception rather than inferred from a log parser. The 18.6 logging configuration describes CSV fields such as sql_state_code, message, and detail, and JSON fields such as state_code, message, and detail; neither format defines the protocol’s constraint_name as a standard field. The targeted log_fields run matched the collector records to the same backend PID, schema/table, constraint, primary message, and DETAIL as the driver diagnostic: PostgreSQL 18.6 exposed both CSV and JSON, while PostgreSQL 10.21 exposed CSV.

The index-build message is deliberately separate:

SQLSTATE: 23505
message_primary: could not create unique index "idx_concurrent"
message_detail: Key (email)=(dup) is duplicated.
source: tuplesortvariants.c / comparetup_index_btree_tiebreak

Do not parse only the English message to classify this error. Branch on SQLSTATE, then use the structured fields and the operation context. Localized message text can change while the five-character SQLSTATE remains the stable protocol value.

Diagnosis

Record the failed statement, SQLSTATE, primary message, detail, hint, schema/table/constraint fields, server version, and transaction status. A driver should expose the original exception; a generic ORM error string may discard fields needed to identify the conflicting object.

For a named table, inspect both constraints and indexes before changing data. These are the diagnostic_catalog_queries statements used by the runner; the disposable run executes them against its accounts table:

SELECT conname, contype, condeferrable, condeferred,
       pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'accounts'::regclass;

SELECT indexrelid::regclass AS index_name,
       indisunique, indisvalid, indisready, indislive,
       pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'accounts'::regclass;

For an ordinary DML error, compare the attempted key with the row protected by the named constraint. Check every unique invariant on the table; a statement that names one conflict target can still violate another unique constraint. For an index-build error, find duplicate keys before retrying the build and inspect pg_index after a concurrent failure.

The runtime case concurrent_unique_conflict used two sessions and an observer. Session A held an uncommitted token='raced'; before A committed, the observer saw B’s statement with wait_event_type=Lock and wait_event=transactionid. After A committed, B received 23505 and was INERROR until rollback. This synchronization is evidence of the ordering; a fixed sleep would not establish it.

Response and repair

Recover the transaction first (cases: explicit_tx_abort_recovery, savepoint_recovery)

With autocommit enabled, the failed operation is complete and the connection was IDLE in the runtime cases. Decide what the application should do with the input, then issue a new command.

Inside an explicit transaction, stop using the connection after the first failure until the failed transaction is handled:

The INSERT INTO items VALUES (3, 'seed') statement below is the natural duplicate trigger; the following ROLLBACK is the required recovery.

CREATE TABLE items(id integer PRIMARY KEY, note text UNIQUE NOT NULL);
INSERT INTO items VALUES (1, 'seed');
BEGIN;
INSERT INTO items VALUES (2, 'outer');
INSERT INTO items VALUES (3, 'seed');
ROLLBACK;
BEGIN;
INSERT INTO items VALUES (2, 'after rollback');
COMMIT;

If only a part of the unit of work is optional, use a savepoint and retain the outer work:

The insert after the savepoint reuses the seeded unique value, so it is the natural 23505 trigger. ROLLBACK TO SAVEPOINT then removes only that failed subtransaction.

CREATE TABLE items(id integer PRIMARY KEY, note text UNIQUE NOT NULL);
INSERT INTO items VALUES (1, 'seed');
BEGIN;
INSERT INTO items VALUES (2, 'outer');
SAVEPOINT unique_case;
INSERT INTO items VALUES (3, 'seed');
-- This statement is expected to return 25P02 while the transaction is failed.
SELECT count(*) FROM items;
ROLLBACK TO SAVEPOINT unique_case;
INSERT INTO items VALUES (3, 'after savepoint');
RELEASE SAVEPOINT unique_case;
COMMIT;

The runner observed 25P02 when it issued a SELECT before rollback, then observed INTRANS after ROLLBACK TO SAVEPOINT. A plain ROLLBACK returns an explicit transaction to IDLE; ROLLBACK TO preserves work before the savepoint.

A deferred constraint changes the failure point (case: deferred_commit_conflict). In the real case, both duplicate inserts succeeded while the connection was INTRANS; COMMIT then raised 23505, returned the connection to IDLE, and left zero rows from that failed top-level transaction. Fix the keys before commit or roll back and retry the entire unit of work.

PL/pgSQL can handle a natural unique violation inside an exception block (case: plpgsql_exception_recovery):

CREATE TABLE items(id integer PRIMARY KEY, note text);
INSERT INTO items(id, note) VALUES (1, 'seed');

CREATE FUNCTION try_insert(wanted integer) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE returned_state text;
BEGIN
    INSERT INTO items(id, note) VALUES (wanted, 'body');
    RETURN 'inserted';
EXCEPTION WHEN unique_violation THEN
    GET STACKED DIAGNOSTICS returned_state = RETURNED_SQLSTATE;
    INSERT INTO items(id, note) VALUES (wanted + 1, 'handler');
    RETURN returned_state;
END
$$;

SELECT try_insert(1);

The protected block is executed with subtransaction behavior. If an error occurs, persistent changes made inside that block are rolled back before the handler runs; changes made before entering the block remain. Keep the handler narrow: if the block contains several statements that can violate different unique constraints, a caught unique_violation does not by itself prove which operation caused it. PostgreSQL’s own PL/pgSQL documentation warns about this shape in generic upsert handlers.

Choose the business operation (case: on_conflict_target_scope)

ON CONFLICT is a tool for an intentional conflict policy, not a general instruction to hide duplicates. A conflict target chooses the arbiter. In the runtime case, an existing row occupied phone='phone-1':

CREATE TABLE accounts(
    id integer PRIMARY KEY,
    email text NOT NULL,
    phone text NOT NULL,
    CONSTRAINT accounts_email_uq UNIQUE (email),
    CONSTRAINT accounts_phone_uq UNIQUE (phone)
);
INSERT INTO accounts VALUES (1, 'existing@example.test', 'phone-1');

INSERT INTO accounts(id,email,phone)
VALUES (2, 'existing@example.test', 'phone-2')
ON CONFLICT (email) DO NOTHING;

-- Handles an email conflict only. A phone-only conflict still raises 23505.
INSERT INTO accounts(id,email,phone)
VALUES (3, 'new@example.test', 'phone-1')
ON CONFLICT (email) DO NOTHING;

-- With no target, DO NOTHING covers a conflict with any usable arbiter.
INSERT INTO accounts(id,email,phone)
VALUES (4, 'third@example.test', 'phone-1')
ON CONFLICT DO NOTHING;

For DO UPDATE, make the update deterministic and check its business result. For an idempotency key, compare the incoming request’s relevant identity and parameters with the stored request, then verify the existing business result before returning “already processed.” A key collision alone is not proof that the earlier request was equivalent.

Repair sequences carefully (case: sequence_lag_repair)

A manually supplied key can leave a sequence behind the table. The controlled runtime case used a non-default sequence:

The second nextval insert below is the natural duplicate trigger. The setval call is followed by a real insert that verifies the repaired next value.

CREATE SEQUENCE ids_seq START WITH 100 INCREMENT BY 7 MINVALUE 100 MAXVALUE 100000;
CREATE TABLE items(id integer PRIMARY KEY, note text);
INSERT INTO items(id,note) VALUES (100, 'explicit');
INSERT INTO items(id,note) VALUES (nextval('ids_seq'), 'generated');
SELECT setval('ids_seq', (SELECT max(id) FROM items), true);
INSERT INTO items(id,note) VALUES (nextval('ids_seq'), 'after repair');

After the controlled setval, the next value was 107. This is a repair pattern with explicit preconditions: quiesce writers, confirm the sequence identity and ownership, handle an empty table without passing an invalid value, and inspect increment, bounds, cache, and is_called. setval is not a universal concurrent repair; sequence changes are not rolled back like ordinary table writes.

Retry only when the operation is retryable

The serialization failure guidance documents a class of cases where concurrent key selection can surface as 23505. When the application has established that meaning, retry the complete transaction, including the key-selection logic, and apply a bounded backoff and an idempotency policy. Do not retry blindly: a duplicate requested by the user can be permanent, and repeated retries can produce the same conflict.

For the two-session case, the conflict happened after a lock-established ordering. The runner does not claim that every application operation with the same code is safe to retry.

Handle failed concurrent index builds (case: index_build_conflict)

The CREATE INDEX documentation explains that a concurrent build can leave an INVALID index when a scan problem such as a uniqueness failure occurs. In this duplicate-scan case, pg_index showed indisvalid=false, indisready=false, indislive=true, and indisunique=true; the regular transactional build left no index after rollback. Resolve duplicates, inspect the actual catalog state, drop a leftover invalid index when appropriate, and retry. Do not generalize this state to every possible CREATE INDEX CONCURRENTLY failure stage.

Versions and boundaries

The same SQLSTATE was observed in both PostgreSQL 10.21 and 18.6. Source line numbers and internal function names differ between those releases; use the SQLSTATE and the operation context rather than a source line as the compatibility contract.

UNIQUE NULLS NOT DISTINCT is available in PostgreSQL 15 and later and must be selected explicitly. The runtime case inserted two NULLs into a default unique column successfully, while a separately declared UNIQUE NULLS NOT DISTINCT constraint raised 23505 on the second NULL. An upgrade does not silently change the old default to NULLS NOT DISTINCT.

The separate boundary comparison records the executed availability and behavior: PostgreSQL 14.24 accepted two ordinary NULL rows but rejected the explicit declaration with 42601 and no 23505; PostgreSQL 15.19 raised 23505 on the second explicit NULL, left the autocommit session IDLE, and committed the valid repair. These records are retained separately and do not inflate the base selected case counts. On another disposable target, check the version before executing the branch: run ordinary_create, ordinary_first, ordinary_second, and ordinary_verify; then run explicit_create. If explicit_create returns the expected PG14 42601, stop that boundary case and do not send explicit_first, explicit_second, explicit_repair, or explicit_verify. Only when explicit_create succeeds on PG15 or later should you continue with the explicit inserts, observe the second-NULL result, and then run the repair and verification steps.

-- ordinary_create
CREATE TABLE ordinary_nulls (external_id integer UNIQUE);
-- ordinary_first
INSERT INTO ordinary_nulls VALUES (NULL);
-- ordinary_second
INSERT INTO ordinary_nulls VALUES (NULL);
-- ordinary_verify
SELECT count(*) FROM ordinary_nulls;
-- explicit_create
CREATE TABLE explicit_nulls (external_id integer, CONSTRAINT nulls_not_distinct_uq UNIQUE NULLS NOT DISTINCT (external_id));
-- explicit_first
INSERT INTO explicit_nulls VALUES (NULL);
-- explicit_second
INSERT INTO explicit_nulls VALUES (NULL);
-- explicit_repair
INSERT INTO explicit_nulls VALUES (1);
-- explicit_verify
SELECT count(*) FROM explicit_nulls;

PostgreSQL 18’s logical conflict reporter uses labels such as insert_exists in its apply context. That message change does not turn the relevant unique conflict into a different SQLSTATE. The current evidence is source and documentation based; the runtime report does not claim a replication topology was tested.

The locked catalogue has a definition-presence observation for 23505 at PostgreSQL 7.4 and through the 8.4.22 pre-9.0 definitions, then in every listed formal snapshot through PostgreSQL 18.6 and the PostgreSQL 19 Beta 3 preview. This is a definition-only presence boundary, not an exact implementation introduction or runtime-use claim.

Sources and evidence

Evidence IDs used by this page are identity.class-and-condition, runtime.dml-template, runtime.protocol-fields, runtime.index-build-template, runtime.logical-apply-sqlstate, runtime.tx-contexts, runtime.retry-boundary, runtime.on-conflict-scope, runtime.sequence-repair-limit, runtime.nulls-choice, runtime.detail-visibility, and runtime.version-boundary in the public evidence JSON. The selected runtime records use the base run for unreplaced cases, 23505-diagnostic-snippet-20260909 for DML, 23505-log-fields-final-20260909 for exact collector correlation, 23505-snippet-contract-20260909 for the registry excerpts, and the manual-boundary runs for explicit transaction and savepoint recovery. The separate boundary records are runtime.23505-boundary-pg14-20260909.pg14 and runtime.23505-boundary-pg15-20260909.pg15; they are a version comparison, not additions to the base case count. Superseded full-run selections remain local audit data.