PostgreSQL 23505 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23505: unique_violation — What It Is and How to Fix It

PostgreSQL error code 23505 (unique_violation) is thrown when an INSERT or UPDATE operation attempts to store a value that already exists in a column (or set of columns) protected by a UNIQUE constraint or unique index. This is a data integrity mechanism built into PostgreSQL to prevent duplicate records. While the error is clear in its meaning, the root cause and the right fix can vary significantly depending on your workload.

Top 3 Causes

1. Direct INSERT Without Duplicate Checking

The most common cause — inserting a row without first verifying uniqueness, especially under concurrent load where application-level checks are insufficient.

-- This will throw 23505 if 'hong@example.com' already exists
INSERT INTO users (email, username)
VALUES ('hong@example.com', 'John');

-- Better approach: use ON CONFLICT (PostgreSQL 9.5+)
INSERT INTO users (email, username)
VALUES ('hong@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

-- Or upsert (update on conflict)
INSERT INTO users (email, username)
VALUES ('hong@example.com', 'John')
ON CONFLICT (email)
DO UPDATE SET username = EXCLUDED.username;

2. Batch Jobs / Data Migrations With Duplicate Source Data

ETL pipelines and migration scripts often fail mid-run and get re-executed, causing already-inserted rows to be inserted again.

-- Remove duplicates from staging data before inserting
INSERT INTO orders (order_no, customer_id, amount)
SELECT order_no, customer_id, amount
FROM (
    SELECT order_no, customer_id, amount,
           ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY created_at DESC) AS rn
    FROM staging_orders
) sub
WHERE rn = 1
ON CONFLICT (order_no) DO NOTHING;

3. Sequence / Serial Out of Sync

After a database restore or manual ID insertion, the sequence may lag behind the actual max ID, causing it to generate already-used values.

-- Check the current max ID
SELECT MAX(id) FROM users;

-- Resync the sequence to the current max
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users), true);

-- Verify all sequences in the public schema
SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'public';

Quick Fix Solutions

-- 1. Identify which constraint is violated
-- The error message will include the constraint name, e.g.:
-- "duplicate key value violates unique constraint users_email_key"

-- 2. Find duplicates before they cause issues
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- 3. List all unique constraints on a table
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass
  AND contype = 'u';

Prevention Tips

Always use ON CONFLICT for idempotent writes.
Make ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE your default pattern for any INSERT that could race with concurrent sessions. Application-level duplicate checks alone cannot prevent Race Conditions.

Keep sequences in sync and validate pipeline data early.
After any restore or bulk load that includes explicit ID values, immediately run setval() to resync affected sequences. For batch pipelines, add a pre-flight query that counts duplicates in your staging table before touching production — catching the problem early is far cheaper than rolling back a failed migration.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.