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

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 357 views

This PostgreSQL error (ERROR 23505) occurs when an INSERT or UPDATE operation tries to add a value that already exists in a column with a unique constraint or primary key. Common causes include out-of-sync sequences, duplicate data, or concurrent inserts. Fixing requires identifying the constraint, checking for existing duplicates, or using INSERT...ON CONFLICT.

Quick fix

Fixes 23505

-- Error example:
-- ERROR:  duplicate key value violates unique constraint "users_email_key"
-- DETAIL:  Key (email)=(john@example.com) already exists.

-- Query the constraint details:
SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'users' AND constraint_name = 'users_email_key';

-- For primary key violations:
SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE table_name = 'your_table' AND constraint_type = 'PRIMARY KEY';
  1. Identify which constraint is being violated from the error message
  2. Check for existing duplicate values in the target column(s)
  3. For SERIAL/BIGSERIAL columns, verify the sequence is in sync
  4. Use INSERT...ON CONFLICT to handle duplicates gracefully
postgresql — error
-- Error example:
-- ERROR:  duplicate key value violates unique constraint "users_email_key"
-- DETAIL:  Key (email)=(john@example.com) already exists.
-- Query the constraint details:
SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'users' AND constraint_name = 'users_email_key';
-- For primary key violations:
SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE table_name = 'your_table' AND constraint_type = 'PRIMARY KEY';
ERROR: duplicate key value violates unique constraint

What this error means

PostgreSQL Error 23505 (Unique Constraint Violation) occurs when you attempt to insert or update a record with a value that violates a unique constraint. This constraint could be a primary key, a unique index, or an explicitly defined unique constraint on one or more columns. The error message typically includes the constraint name, like "ERROR: duplicate key value violates unique constraint "users_email_key"" or "ERROR: duplicate key value violates unique constraint "table_pkey"" for primary keys. Unlike application-level validation, PostgreSQL enforces these constraints at the database level, ensuring data integrity even if buggy code or concurrent operations attempt to bypass safeguards. The error is PostgreSQL's way of protecting your data—it refuses to compromise data consistency by allowing duplicates. Understanding when and why this error occurs is essential for building reliable database applications, especially in multi-user environments where concurrent inserts are common.

Typical symptoms

  • INSERT operation fails with constraint violation error message naming the specific constraint
  • UPDATE operation fails when changing a column value to match an existing row
  • Application receives a 23505 error code from the database driver
  • Sequence out of sync: auto-increment IDs conflict with manually inserted rows
  • After bulk data import, INSERT statements fail sporadically on different rows
  • Race conditions cause occasional constraint violations in high-concurrency scenarios
  • TRUNCATE without RESTART IDENTITY allows old sequence values to be reused

Common causes

  1. Sequence out of sync with actual data: manually inserted IDs advance beyond the sequence's current value, causing new auto-generated IDs to conflict (most common in SERIAL/BIGSERIAL columns)
  2. Bulk data import or migration contains duplicate values that violate the unique constraint
  3. Concurrent INSERT operations by multiple clients attempting to insert the same value simultaneously
  4. Application logic error inserting the same value twice without checking for existence first
  5. Table truncated without RESTART IDENTITY, allowing the sequence to restart from 1 and generate IDs that already exist in the table
  6. Migrating data from another database where unique constraint rules differ or duplicates were allowed
  7. NULL values in unique constraints where NULLS NOT DISTINCT is not configured (by default, multiple NULLs are allowed even in unique columns)
  8. Index corruption causing PostgreSQL to incorrectly report a duplicate when one does not actually exist

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

1Identify which constraint is being violated from the error message

The error message includes the constraint name, which tells you which column(s) are causing the problem.

sql
-- Error example:
-- ERROR:  duplicate key value violates unique constraint "users_email_key"
-- DETAIL:  Key (email)=(john@example.com) already exists.

-- Query the constraint details:
SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'users' AND constraint_name = 'users_email_key';

-- For primary key violations:
SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE table_name = 'your_table' AND constraint_type = 'PRIMARY KEY';

Note the column(s) involved—this guides your next investigation.

2Check for existing duplicate values in the target column(s)

Find which values already exist to understand the conflict.

sql
-- Find all duplicates in a column:
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- For composite unique constraints (multiple columns):
SELECT user_id, post_id, COUNT(*) as count
FROM likes
GROUP BY user_id, post_id
HAVING COUNT(*) > 1;

-- Check if the specific value you're trying to insert already exists:
SELECT * FROM users WHERE email = 'john@example.com';

If duplicates exist in the table, you must decide: delete the duplicates, or keep only one copy before proceeding.

3For SERIAL/BIGSERIAL columns, verify the sequence is in sync

Out-of-sync sequences are the most common cause when inserting new (non-duplicate) data. Compare the maximum ID in the table with the next value the sequence will generate.

sql
-- Find the sequence name (typically table_id_seq):
SELECT pg_get_serial_sequence('users', 'id');
-- Result: public.users_id_seq

-- Check the sequence's current value:
SELECT nextval('users_id_seq');

-- Check the maximum ID in the table:
SELECT MAX(id) FROM users;

-- If nextval() returns a value LESS than or equal to MAX(id), the sequence is out of sync.
-- Fix by resetting the sequence to the next available value:
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users) + 1);

-- Verify the fix:
SELECT nextval('users_id_seq');
-- This should now be MAX(id) + 2 (one call used +1, the next call adds another +1)

Always be conservative: use MAX(id) + 1, not just MAX(id), to leave room for the next insert.

4Use INSERT...ON CONFLICT to handle duplicates gracefully

The ON CONFLICT clause prevents errors when duplicates are expected or acceptable. Choose the appropriate action:

Option A: Skip the insert if a conflict occurs (DO NOTHING)

sql
INSERT INTO users (id, email, name)
VALUES (1, 'john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;
-- If john@example.com exists, the INSERT is silently skipped

Option B: Update the existing row instead (UPSERT)

sql
INSERT INTO users (id, email, name)
VALUES (1, 'john@example.com', 'John Doe')
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW();
-- If john@example.com exists, update the name instead

Option C: For composite unique constraints

sql
INSERT INTO likes (user_id, post_id, created_at)
VALUES (5, 10, NOW())
ON CONFLICT (user_id, post_id) DO NOTHING;

The ON CONFLICT clause is the recommended approach for high-volume inserts where duplicates are unavoidable.

5Remove duplicate rows if they exist in the table

If your constraint allows only one copy of each value, delete the extra rows. The safest approach uses ctid (internal row ID) to preserve the most recent or preferred row.

sql
-- Delete duplicate rows, keeping only the first one (lowest ctid):
DELETE FROM users a
USING users b
WHERE a.ctid > b.ctid
  AND a.email = b.email;

-- Alternative: keep the most recent row by created_at:
DELETE FROM users a
USING users b
WHERE a.email = b.email
  AND a.created_at < b.created_at;

-- Verify the duplicates are gone:
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Always backup your table before running DELETE statements. Test on a development copy first.

6For TRUNCATED tables without RESTART IDENTITY, reset the sequence

If you truncated the table and the sequence was not reset, sequences will restart from 1 and conflict with old data. Fix by restarting the sequence with RESTART IDENTITY.

sql
-- Properly truncate and reset the sequence:
TRUNCATE TABLE users RESTART IDENTITY;

-- If the table is already truncated, manually reset:
SELECT setval('users_id_seq', 1);

-- For tables with foreign key constraints, you may need CASCADE:
TRUNCATE TABLE users CASCADE RESTART IDENTITY;

RESTART IDENTITY resets all sequences for that table to their initial values.

Advanced notes

For tables using UUIDs instead of SERIAL: UUID columns never have out-of-sync sequence issues since they don't use sequences. If you see duplicate key violations with UUIDs, the application is generating duplicate UUIDs (extremely unlikely with cryptographically secure UUID libraries) or inserting manually without checking for duplicates first.

PostgreSQL allows multiple NULL values in unique columns by default (NULL is not equal to NULL in standard SQL). If your constraint should prevent duplicate NULLs, use the NULLS NOT DISTINCT clause when creating the constraint: CONSTRAINT unique_email UNIQUE (email) NULLS NOT DISTINCT. This is useful for fields like "secondary_email" where duplicates and NULLs are both problems.

In high-concurrency environments, race conditions can cause violations even if each client checks for existence first. Between the check and the insert, another client may insert the same value. Use ON CONFLICT (DO NOTHING or DO UPDATE) or serializable transactions to handle this safely without application-level retry loops.

Index corruption is rare but possible. If you suspect it, rebuild the unique index: REINDEX INDEX index_name; This scans the table and rebuilds the index, validating consistency.

For performance: if you're doing bulk inserts with expected duplicates, use ON CONFLICT to avoid failed transactions. Failed inserts cause transaction rollback and wasted work. ON CONFLICT DO NOTHING is much faster than catching exceptions in the application.

Frequently asked questions

What does "23505" mean?
PostgreSQL Error 23505 (Unique Constraint Violation) occurs when you attempt to insert or update a record with a value that violates a unique constraint. This constraint could be a primary key, a unique index, or an explicitly defined unique constraint on one or more columns. The error message typically includes the constraint name, like "ERROR: duplicate key value violates unique constraint "users_email_key"" or "ERROR: duplicate key value violates unique constraint "table_pkey"" for primary keys.
How do I fix "23505"?
This PostgreSQL error (ERROR 23505) occurs when an INSERT or UPDATE operation tries to add a value that already exists in a column with a unique constraint or primary key. Common causes include out-of-sync sequences, duplicate data, or concurrent inserts. Fixing requires identifying the constraint, checking for existing duplicates, or using INSERT...ON CONFLICT.
What causes "23505"?
Sequence out of sync with actual data: manually inserted IDs advance beyond the sequence's current value, causing new auto-generated IDs to conflict (most common in SERIAL/BIGSERIAL columns) Bulk data import or migration contains duplicate values that violate the unique constraint Concurrent INSERT operations by multiple clients attempting to insert the same value simultaneously
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