PostgreSQL Error 23505: Unique Violation (Fix Guide)
Fix PostgreSQL Error 23505 unique constraint violation. Learn ON CONFLICT DO UPDATE (upsert), handling duplicates in bulk inserts, and constraint management.
The Error Message
ERROR: duplicate key value violates unique constraint "constraint_name"
What Causes PostgreSQL 23505?
PostgreSQL Error 23505 fires when an INSERT or UPDATE would create a duplicate value in a column (or combination of columns) that has a UNIQUE constraint or is a PRIMARY KEY.
Common Causes
Inserting duplicate primary key
Manually specifying an ID that already exists in the table.
Unique column conflict
Inserting a row where a unique column (email, username) already has that value.
Sequence out of sync
The auto-increment sequence is behind the actual max ID, causing collisions.
Concurrent inserts
Two transactions inserting the same unique value at the same time.
How to Fix It
Step 1: Use ON CONFLICT DO NOTHING
The row is simply skipped if the email already exists. No error thrown.
-- Skip duplicates silently:
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;Step 2: Use ON CONFLICT DO UPDATE (upsert)
EXCLUDED refers to the row that was rejected. This is the standard upsert pattern in PostgreSQL.
-- Update existing row:
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John Updated', NOW())
ON CONFLICT (email) DO UPDATE SET
name = EXCLUDED.name,
updated_at = EXCLUDED.updated_at;Step 3: Fix sequence out of sync
If you imported data with explicit IDs, the sequence may be behind. This resyncs it.
-- Reset sequence to max ID:
SELECT setval(
pg_get_serial_sequence('users', 'id'),
(SELECT MAX(id) FROM users)
);Step 4: Check existing duplicates
Before adding a unique constraint, check if duplicates already exist in the data.
-- Find duplicates:
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;How to Prevent This Error
Always use ON CONFLICT for insert operations that might encounter duplicates. Keep sequences in sync after bulk data imports. Design unique constraints during schema planning, not after.
Fix PostgreSQL Errors with AI2SQL
Instead of debugging SQL syntax manually, describe what you need in plain English and let AI2SQL generate the correct query for PostgreSQL.
No credit card required
Frequently Asked Questions
What does PostgreSQL Error 23505 mean?
Error 23505 means you tried to insert or update a value that already exists in a column with a UNIQUE constraint. PostgreSQL prevents duplicate values in unique columns.
What's the difference between ON CONFLICT DO NOTHING and DO UPDATE?
DO NOTHING silently skips the conflicting row. DO UPDATE (upsert) updates the existing row with new values. Use DO UPDATE when you want to keep data fresh.
Can AI2SQL generate upsert queries for PostgreSQL?
Yes. Describe your logic like 'insert user if not exists, otherwise update name' and AI2SQL generates the correct ON CONFLICT DO UPDATE syntax.