- Software and databases
- PostgreSQL
- Databases
PostgreSQL 23505: unique constraint violation
Risk level
Low
Usually safe to check without specialist tools.
Urgency
Medium
Quick answer
SQLSTATE 23505 is unique_violation. An INSERT or UPDATE attempted to create a value combination that conflicts with a unique constraint or unique index.
Safety first
Safe checks you can perform
- Read the full database error fields
- Inspect the named uniqueness rule
- Decide whether the duplicate is invalid or expected
Leave these tasks to a qualified professional
- Do not attempt this professional task: Review the write path and concurrency model
Where this code applies
A code is meaningful only inside the right product and version context.
Primary scope
- System
- Software and databases
- Brand
- PostgreSQL
- Product type
- Databases
Known code variants
- PostgreSQL 23505
- SQLSTATE 23505
- unique_violation
What it means
This is a Class 23 integrity constraint violation. Correct handling depends on whether the duplicate is invalid data an idempotent repeat or an expected conflict that should use an explicit ON CONFLICT policy.
Warnings and stop conditions
- Higheditorial warning
Do not delete rows or drop a unique constraint as a generic fix for 23505. First identify the named invariant and decide the intended conflict behavior.
Evidence: PostgreSQL current error codes appendix · PostgreSQL current INSERT and ON CONFLICT documentation
Symptoms and causes
Probable causes · in order
- Unique constraint violationConfirmed
PostgreSQL assigns SQLSTATE 23505 to unique_violation in the Class 23 integrity-constraint family.
Evidence: PostgreSQL current error codes appendix
- INSERT or UPDATE conflicts with an existing unique keyCommon
A proposed row duplicates a key combination protected by a unique constraint or unique index.
Evidence: PostgreSQL current error codes appendix · PostgreSQL current INSERT and ON CONFLICT documentation
- Expected concurrency conflict lacks an explicit policyPossible
When concurrent or repeated writes are valid business behavior the statement may need a deliberate ON CONFLICT action rather than a generic retry.
Evidence: PostgreSQL current INSERT and ON CONFLICT documentation
Safe checks, step by step
- 1
Read the full database error fields
Tools required
Capture SQLSTATE 23505 plus the constraint name table and detail exposed by the PostgreSQL driver. Do not diagnose from the numeric code alone.
Redact production values and credentials before sharing logs.
Evidence: PostgreSQL current error codes appendix
- 2
Inspect the named uniqueness rule
Tools required
Confirm which columns or expression the named constraint or index protects and compare the attempted key with the existing row.
Do not drop a uniqueness rule merely to silence the exception.
Evidence: PostgreSQL current error codes appendix
- 3
Decide whether the duplicate is invalid or expected
Tools required
Correct invalid input at its source. If the conflict is an expected idempotent or upsert case define the exact DO NOTHING or DO UPDATE behavior and conflict target.
An overly broad conflict handler can hide genuine data-quality defects.
Evidence: PostgreSQL current INSERT and ON CONFLICT documentation
Virtual technician
Does 23505 still appear after these checks?
Answer a few questions to narrow down the likely cause and the safest next action. Guidance only - it never replaces a professional.
For professionals only
- Review the write path and concurrency modelverified
A database engineer should verify transaction boundaries idempotency keys conflict targets and the business invariant behind the unique constraint when 23505 recurs under load.
Why: Repeated 23505 errors can be an application design issue rather than a database fault.
Evidence: PostgreSQL current INSERT and ON CONFLICT documentation
Sources and technical references
- 1
PostgreSQL current error codes appendix
PostgreSQL Global Development Group · 2026 · official_doc
- 2
PostgreSQL current INSERT and ON CONFLICT documentation
PostgreSQL Global Development Group · 2026 · official_doc
Was this entry useful?