SQL Error: Duplicate Key (Fix + Examples)

Getting the "duplicate key value violates unique constraint" error in SQL? This guide explains what causes this error and how to fix it with practical examples.

What Causes This Error?

The duplicate key error (23505) occurs when:

• Inserting a row with an existing primary key value
• Inserting data that violates a unique index
• Sequence not properly synchronized after bulk import
• Concurrent inserts creating race conditions

How to Fix It

1. Use INSERT ... ON CONFLICT (column) DO UPDATE for upsert operations.
2. Check if the value already exists before inserting.
3. If using sequences, reset with SELECT setval('sequence_name', (SELECT MAX(id) FROM table)).
4. Consider using UUID instead of auto-increment for distributed systems.

Example: Wrong vs Correct

❌ Code That Causes the Error

INSERT INTO users (id, email) VALUES (1, 'new@email.com');
-- Error: duplicate key value violates unique constraint "users_pkey"

✅ Corrected Code

INSERT INTO users (id, email) VALUES (1, 'new@email.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
-- Correct: upsert pattern handles duplicates

Quick Checklist

• [ ] Verify column/table names are spelled correctly
• [ ] Check data types match expected values
• [ ] Review query syntax for missing keywords
• [ ] Ensure referenced tables/columns exist

Related SQL Errors

If you're troubleshooting SQL errors, you might also encounter: