PostgreSQL: duplicate key value violates unique constraint
ERROR: duplicate key value violates unique constraint "<constraint>"You tried to insert or update a row whose value already exists in a unique or primary-key column. Either handle the conflict with an upsert (ON CONFLICT), or, if a bulk load left your sequence behind the real max id, resync the sequence.
What this error means
A unique constraint (including every primary key) guarantees no two rows share the same value in the constrained columns. When a write would break that promise, Postgres rejects it and names the constraint, plus a DETAIL line showing the exact key that clashed.
A subtle version of this happens after importing rows with explicit ids: the table's sequence still starts low, so the next auto-generated id collides with an imported one.
How to fix it
You want insert-or-update behavior
Use ON CONFLICT to turn the collision into an update (upsert) or a no-op, atomically.
INSERT INTO users (email, name)
VALUES ('a@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;You just want to skip duplicates
DO NOTHING inserts new rows and silently ignores clashes.
INSERT INTO users (email, name)
VALUES ('a@example.com', 'Ada')
ON CONFLICT (email) DO NOTHING;A sequence is out of sync after a bulk import
Reset the sequence to just past the current maximum id so new inserts stop colliding.
SELECT setval(
pg_get_serial_sequence('users', 'id'),
(SELECT max(id) FROM users)
);How to avoid it next time
- Let the database enforce uniqueness and catch 23505, rather than doing a check-then-insert that races under concurrency.
- After any load that sets explicit primary keys, resync the table's sequence.
Frequently asked questions
What is an upsert?
An insert that updates the existing row instead of failing when it conflicts. In Postgres you write it as INSERT ... ON CONFLICT ... DO UPDATE.
Why did this start after importing data?
Imports often set the id column explicitly, but the sequence is not advanced. The next generated id repeats one that was imported. Resync the sequence with setval.
Stop struggling with SQL
Use FluentDB, the AI database client for macOS.
Related errors
Part of the PostgreSQL error reference. Last reviewed 2026-07-31.