PostgreSQL Error 23505: Duplicate Key Violates Unique Constraint

PostgreSQL SQLSTATE 23505 (unique_violation) means an INSERT or UPDATE would duplicate a value protected by a primary key, unique constraint, or unique index. A common message is duplicate key value violates unique constraint. PostgreSQL reports the constraint name and, when available, the conflicting key in separate error fields; see the PostgreSQL 18 error code appendix.

Use the constraint name and DETAIL text to identify which key conflicted. For the basics of how PostgreSQL enforces uniqueness, see PostgreSQL unique indexes.

Find the conflicting row or values

For a unique email constraint on public.accounts, query the value shown in the error detail:

SELECT account_id, email
FROM public.accounts
WHERE email = '[email protected]';

Error 23505 can also occur during an UPDATE that changes a key to a value already used by another row, or when adding a unique constraint to a table that already contains duplicates. For a composite key, group by the same columns in the unique index:

SELECT tenant_id, external_id, COUNT(*) AS duplicate_count
FROM public.imported_records
GROUP BY tenant_id, external_id
HAVING COUNT(*) > 1;

If the error names a constraint or index, inspect the table definition and its indexes. In psql, \d public.accounts shows the table’s constraints and indexes. You can also query pg_indexes or pg_constraint when using another client.

Choose whether duplicates should fail, be skipped, or update a row

If a duplicate is invalid input, keep the unique constraint and handle the error in the application. Do not remove the constraint just because a pre-insert SELECT found no matching row: two concurrent requests can both pass that check, while the unique key is what prevents both from storing the same value.

If a duplicate is expected and should be ignored, use ON CONFLICT DO NOTHING:

INSERT INTO public.accounts (email)
VALUES ('[email protected]')
ON CONFLICT (email) DO NOTHING;

If the existing row should be updated, specify exactly which columns may change:

INSERT INTO public.accounts (email, display_name)
VALUES ('[email protected]', 'Alex')
ON CONFLICT (email)
DO UPDATE SET display_name = EXCLUDED.display_name;

EXCLUDED refers to the row proposed for insertion. ON CONFLICT DO UPDATE provides an atomic insert-or-update outcome, but it can overwrite existing data, so use it only when that behavior is intended. See the PostgreSQL 18 INSERT reference and the PostgreSQL ON CONFLICT tutorial.

For a composite unique key, the conflict target must identify the same key columns, for example ON CONFLICT (tenant_id, external_id). For expression or partial unique indexes, use an appropriate conflict target or name the constraint when available.

Check for a sequence that is behind imported IDs

If the duplicate is on a SERIAL or identity primary key, and rows were imported with explicit IDs, the associated sequence may be behind the largest stored ID. Check the sequence associated with the column and the greatest ID:

SELECT
  pg_get_serial_sequence('public.accounts', 'account_id') AS id_sequence,
  MAX(account_id) AS greatest_existing_id
FROM public.accounts;

Use the returned id_sequence name to inspect its current state. For a conventional sequence named public.accounts_account_id_seq, for example:

SELECT last_value, is_called
FROM public.accounts_account_id_seq;

Compare the sequence’s next value with greatest_existing_id, accounting for its start value and increment. If the sequence is behind, have a database administrator reseed it to match the table’s actual data. Do this only after confirming the sequence configuration and controlling concurrent inserts: PostgreSQL sequence changes made by setval are visible immediately and are not undone if the transaction rolls back. See the PostgreSQL 18 documentation for pg_get_serial_sequence and setval.

For the general insert-or-update syntax, see PostgreSQL INSERT ... ON CONFLICT. For unique-key design, see PostgreSQL unique indexes.