unique_violation Unique Violation — SQLSTATE 23505

A row violates a UNIQUE constraint or unique index — the key already exists.

• The error is written to the server log and returned to the client carrying SQLSTATE 23505.
• Any driver (libpq, JDBC, psycopg, npgsql, pgx) surfaces this code in its error object so you can branch on it programmatically.
• PL/pgSQL can trap it by name: EXCEPTION WHEN unique_violation THEN.

Severity: ERROR  |  PostgreSQL versions: 12, 13, 14, 15, 16, 17

Reproduce with the exact statement and read the full message in the server log (raise log_min_messages / set log_min_error_statement for more context).

An INSERT or UPDATE produced a duplicate value in a column or column set covered by a unique constraint or index (including the primary key).

Common causes:

• Inserting a key that already exists.
• Concurrent inserts racing on the same value.
• A sequence or identity out of sync after a manual load or restore.
• Case or whitespace differences that were not deduplicated.

Steps to resolve 23505:

1. Use upsert: INSERT ... ON CONFLICT (col) DO NOTHING or DO UPDATE SET ....
2. Check existence first, or catch and handle the duplicate in application code.
3. Resync the sequence after manual loads: SELECT setval(pg_get_serial_sequence('t','id'), MAX(id)) FROM t;.
4. Identify the offending constraint from the error detail and deduplicate the source data.

Reference: PostgreSQL error codes — Class 23 (Integrity Constraint Violation).