How to fix ERROR: duplicate key value violates unique constraint in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 244 views

This PostgreSQL error occurs when you try to insert or update a record with a value that already exists in a column with a unique constraint. Fix it by checking the constraint, handling duplicates with upsert, or resyncing sequences if using auto-increment columns.

Quick fix

Fixes 23505

ERROR: duplicate key value violates unique constraint "users_email_key"
  1. Identify which constraint is being violated from the error message
  2. Check if the duplicate value already exists in the table
  3. Use INSERT ... ON CONFLICT DO NOTHING to skip duplicates
  4. Use INSERT ... ON CONFLICT DO UPDATE for upsert behavior
postgresql — error
ERROR: duplicate key value violates unique constraint "users_email_key"
ERROR: duplicate key value violates unique constraint

What this error means

PostgreSQL error code 23505 (unique_violation) occurs when you attempt to insert or update a record with a value that already exists in a column that has a unique constraint. Unique constraints protect data integrity by preventing duplicate values in columns like primary keys, explicit unique indexes, or composite constraints. The error message typically shows which specific constraint was violated (e.g., "duplicate key value violates unique constraint \"users_email_key\""), helping you identify the problematic column.

Typical symptoms

  • INSERT or UPDATE queries fail with "ERROR: duplicate key value violates unique constraint"
  • Error message includes the constraint name in quotes
  • Application receives error code 23505 from the database
  • Bulk data imports fail partway through
  • Auto-increment ID inserts fail even though you're not specifying an ID

Common causes

  1. Attempting to insert a value that already exists in a unique or primary key column
  2. Primary key sequence out of sync with table data after bulk imports or manual ID inserts
  3. Race conditions when multiple transactions insert the same value concurrently
  4. Composite unique constraint violated by one of the columns in the constraint
  5. Sequence not reset after TRUNCATE table without RESTART IDENTITY option
  6. Stale data from a database restore or dump where sequences were not updated

How to fix "ERROR: duplicate key value violates unique constraint"

1Identify which constraint is being violated from the error message

The PostgreSQL error includes the constraint name. Look for text like:

bash
ERROR: duplicate key value violates unique constraint "users_email_key"

The constraint name tells you which column(s) are causing the conflict. In this example, it's the email column with the unique constraint "users_email_key".

2Check if the duplicate value already exists in the table

Query the table to find the existing record with the conflicting value:

sql
SELECT * FROM users WHERE email = 'duplicate@example.com';

This helps you decide whether to update the existing record, skip the insert, or use upsert logic instead.

3Use INSERT ... ON CONFLICT DO NOTHING to skip duplicates

If you want to silently skip records that would violate the constraint:

sql
INSERT INTO users (email, name) VALUES ('user@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

This is the most efficient approach when you don't need to update existing rows. It avoids throwing errors and wasting transaction IDs.

4Use INSERT ... ON CONFLICT DO UPDATE for upsert behavior

If the record should be updated if it exists, or inserted if it doesn't:

sql
INSERT INTO users (email, name) VALUES ('user@example.com', 'John')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

For composite unique constraints, specify all columns in the constraint:

sql
INSERT INTO user_projects (user_id, project_id, role) 
VALUES (1, 5, 'admin')
ON CONFLICT (user_id, project_id) DO UPDATE SET role = EXCLUDED.role;
5Check and reset the primary key sequence if using SERIAL/BIGSERIAL

If you're getting duplicate key errors on auto-increment ID columns, the sequence may be out of sync. Compare the maximum ID in the table with the next sequence value:

sql
SELECT MAX(id) FROM users;
SELECT nextval('users_id_seq');

If the sequence value is lower than the max ID, reset it:

sql
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users) + 1);

After this, new inserts will use IDs that don't conflict with existing rows.

6Check for and fix composite constraint issues

If you have a composite unique constraint on multiple columns, all columns in the constraint must be unique as a group. View the constraint definition:

sql
\d+ users

Look for constraints like "UNIQUE KEY (user_id, project_id)". Even if individual columns have duplicates, they're only a problem if the exact combination already exists.

Advanced notes

Out-of-sync sequences are extremely common after bulk imports, database restores, or when manually inserting specific ID values. When you use TRUNCATE without RESTART IDENTITY, the sequence retains its old high value, causing conflicts on re-insert. PostgreSQL won't auto-reset sequences like some other databases do. For concurrent applications, race conditions can still cause 23505 errors even with correct INSERT ON CONFLICT logic if timing is poor—use exponential backoff retry logic on the application side. The EXCLUDED keyword references the values that would have been inserted, making it possible to update with the new data. If you have multiple unique constraints on a table, PostgreSQL will report which one was violated, but you should ensure your logic accounts for all of them.

Frequently asked questions

What does "23505" mean?
PostgreSQL error code 23505 (unique_violation) occurs when you attempt to insert or update a record with a value that already exists in a column that has a unique constraint. Unique constraints protect data integrity by preventing duplicate values in columns like primary keys, explicit unique indexes, or composite constraints. , "duplicate key value violates unique constraint \"users_email_key\""), helping you identify the problematic column.
How do I fix "23505"?
This PostgreSQL error occurs when you try to insert or update a record with a value that already exists in a column with a unique constraint. Fix it by checking the constraint, handling duplicates with upsert, or resyncing sequences if using auto-increment columns.
What causes "23505"?
Attempting to insert a value that already exists in a unique or primary key column Primary key sequence out of sync with table data after bulk imports or manual ID inserts Race conditions when multiple transactions insert the same value concurrently
Is the "23505" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading