Error 23505 (unique_violation) appears when an INSERT or UPDATE tries to create a duplicate value in a column or index defined as UNIQUE.
PostgreSQL Error 23505 – unique_violation – means the row you are inserting or updating would duplicate a value in a UNIQUE or PRIMARY KEY index. Identify the conflicting key with the error detail, then either change the data, remove the duplicate row, or use ON CONFLICT handling to upsert.
PostgreSQL Error 23505
PostgreSQL raises SQLSTATE 23505 when a statement would insert or update a row whose key already exists in a UNIQUE index or PRIMARY KEY. The engine rejects the write to protect data integrity.
The error stops the transaction at the failing statement, so downstream queries will not run until the conflict is resolved.
Fixing it quickly keeps applications available and prevents partial data writes.
Unique indexes guarantee that each stored value or combination of values appears only once. An INSERT with a duplicate primary-key value, or an UPDATE that sets a column to an existing unique value, triggers the violation.
Concurrent transactions can also collide.
If two sessions insert the same key before either commits, the second commit fails with 23505.
First read the DETAIL line in the error message to find the conflicting key. Modify the incoming data so the key is unique or delete/merge the existing row.
Use INSERT ... ON CONFLICT ... DO UPDATE (UPSERT) to merge duplicates automatically.
For bulk loads, stage data in a temp table, deduplicate, then insert.
Failed user signup because email address column is UNIQUE. Solution – check existence before insert or use ON CONFLICT DO NOTHING.
ETL job reruns nightly and re-inserts primary keys already present. Solution – truncate staging table or run ON CONFLICT DO UPDATE.
Validate data in application code or database constraints before writing. Wrap INSERTS in ON CONFLICT logic.
Use SERIAL/IDENTITY or UUID default generators for primary keys to avoid manual duplication.
When bulk loading, isolate into a staging area, add DISTINCT, and run dedup queries before merging.
Error 23503 – foreign_key_violation – appears when a referenced key is missing. Fix by inserting the parent row first or enabling DEFERRABLE constraints.
Error 23514 – check_violation – fires when CHECK constraints fail. Review the CHECK expression and adjust data accordingly.
.
The DETAIL section of the error message shows the exact column values that duplicated. Query the table with those values to locate the row.
Yes. Use INSERT ... ON CONFLICT DO NOTHING to silently skip rows that would violate uniqueness.
ON CONFLICT adds a small overhead for conflict checking but usually outperforms retry loops in application code.
Galaxy's AI copilot suggests UPSERT patterns and highlights unique indexes while you type, reducing accidental duplicate inserts.
.avif)
.avif){kind=logo}
