The error occurs when an INSERT or UPDATE tries to store a duplicate value in a column or index declared as UNIQUE.
UNIQUE violation (SQLSTATE 23505) happens when an INSERT or UPDATE writes a duplicate value into a UNIQUE-constrained column or index. Remove or alter the duplicate data, or use INSERT … ON CONFLICT to handle collisions, to resolve the error.
ERROR: duplicate key value violates unique constraint "<constraint_name>" (SQLSTATE 23505)
UNIQUE violation, SQLSTATE 23505, signals that a row being inserted or updated duplicates a value protected by a UNIQUE constraint or unique index.
The server aborts the statement and, inside a transaction, marks the entire transaction as failed until you ROLLBACK or use a savepoint.
The most common trigger is inserting a row whose primary-key or unique-key value already exists in the target table.
Updating a non-unique row so that its new value collides with another row covered by the same UNIQUE constraint also raises the error.
First, identify which column or index is named in the error message.
Query pg_constraint or pg_indexes for details.
Next, locate the conflicting data with a SELECT filtering on the duplicate key value, then decide whether to delete, modify, or merge records.
Bulk import scripts often ignore existing keys—use INSERT … ON CONFLICT DO NOTHING to skip duplicates safely.
Upserts require INSERT … ON CONFLICT DO UPDATE to merge incoming data with existing rows.
Always create deterministic primary keys (e.g., UUID v4) client-side or with DEFAULT gen_random_uuid() to eliminate race-condition duplicates.
Wrap high-write operations in explicit transactions and add retries with exponential back-off to handle occasional conflicts gracefully.
NOT NULL violation (SQLSTATE 23502) arises when NULL is inserted where it is forbidden—set a default value or supply data.
Foreign-key violation (SQLSTATE 23503) happens when referenced rows are missing—insert parent rows first or use DEFERRABLE constraints.
.
Yes. In PostgreSQL, any error inside a transaction marks the transaction as failed; you must ROLLBACK or use a savepoint.
The error provides the duplicate key value. Query the table filtering on that value to locate the conflicting record.
Use INSERT … ON CONFLICT DO NOTHING to skip rows that would violate the UNIQUE constraint.
No. Additional indexes do not change the UNIQUE constraint’s logic; ensure only one unique index governs the intended columns.
.png)
.avif){kind=logo}
