# 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 {#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](../data/evidence/23505.json); superseded summaries and raw JSONL remain local audit data.

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| 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 | `—` |

<!-- source facts: data/errcodes/23505.json -->
<!-- END SQLSTATE FACTS -->

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 {#meaning}

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 {#messages}

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:

```text
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](https://www.postgresql.org/docs/18/protocol-error-fields.html) 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:

```text
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 {#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:

<!-- BEGIN SQLSTATE SNIPPET: diagnostic_catalog_queries -->
```sql
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;
```
<!-- END SQLSTATE SNIPPET -->

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 {#response}

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

<!-- BEGIN SQLSTATE SNIPPET: explicit_tx_abort_recovery -->
```sql
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;
```
<!-- END SQLSTATE SNIPPET -->

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.

<!-- BEGIN SQLSTATE SNIPPET: savepoint_recovery -->
```sql
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;
```
<!-- END SQLSTATE SNIPPET -->

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`):

<!-- BEGIN SQLSTATE SNIPPET: plpgsql_exception_recovery -->
```sql
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);
```
<!-- END SQLSTATE SNIPPET -->

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'`:

<!-- BEGIN SQLSTATE SNIPPET: on_conflict_target_scope -->
```sql
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;
```
<!-- END SQLSTATE SNIPPET -->

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.

<!-- BEGIN SQLSTATE SNIPPET: sequence_lag_repair -->
```sql
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');
```
<!-- END SQLSTATE SNIPPET -->

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](https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html) 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](https://www.postgresql.org/docs/18/sql-createindex.html) 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 {#versions}

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.

<!-- BEGIN SQLSTATE SNIPPET: nulls_not_distinct_version_boundary -->
```sql
-- 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;
```
<!-- END SQLSTATE SNIPPET -->

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.

## Related codes {#related}

- [`25P02` — in_failed_sql_transaction](../25p02/) explains the follow-on error after an unhandled statement failure in an explicit transaction.
- [`40001` — serialization_failure](../40001/) and [`40P01` — deadlock_detected](../40p01/) are the primary retry-related transaction errors; PostgreSQL also documents selected `23505` key-selection races.
- [`23503` — foreign_key_violation](../23503/) is another Class 23 integrity error, but a missing referenced row has different diagnosis and repair steps.

## Sources and evidence {#sources}

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](../data/evidence/23505.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.

- [PostgreSQL 18.6 `errcodes.txt` at the fixed source commit](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L235-L241) — Class 23 and `unique_violation` identity.
- [PostgreSQL 18.6 nbtree unique check at the fixed source commit](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/access/nbtree/nbtinsert.c#L640-L674) — DML message and protocol object attachment.
- [PostgreSQL 18.6 index value description at the fixed source commit](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/access/index/genam.c#L155-L275) — detail visibility limits.
- [PostgreSQL 18.6 index-build error at the fixed source commit](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/sort/tuplesortvariants.c#L1670-L1694) — separate build template.
- [PostgreSQL 18.6 logical conflict reporter at the fixed source commit](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/replication/logical/conflict.c#L102-L180) — apply labels and SQLSTATE mapping.
- [INSERT command reference](https://www.postgresql.org/docs/18/sql-insert.html) — conflict-target scope and arbiter restrictions.
- [CREATE INDEX command reference](https://www.postgresql.org/docs/18/sql-createindex.html) — concurrent-build phases and invalid-index behavior.
- [Error and Notice Message Fields](https://www.postgresql.org/docs/18/protocol-error-fields.html) and [server log configuration](https://www.postgresql.org/docs/18/runtime-config-logging.html) — protocol versus collector fields.
- [PL/pgSQL control structures](https://www.postgresql.org/docs/18/plpgsql-control-structures.html), [transaction tutorial](https://www.postgresql.org/docs/18/tutorial-transactions.html), and [sequence functions](https://www.postgresql.org/docs/18/functions-sequence.html) — recovery and controlled repair semantics.