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.
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.
The PostgreSQL error includes the constraint name. Look for text like:
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".
Query the table to find the existing record with the conflicting value:
SELECT * FROM users WHERE email = '[email protected]';This helps you decide whether to update the existing record, skip the insert, or use upsert logic instead.
If you want to silently skip records that would violate the constraint:
INSERT INTO users (email, name) VALUES ('[email protected]', '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.
If the record should be updated if it exists, or inserted if it doesn't:
INSERT INTO users (email, name) VALUES ('[email protected]', 'John')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();For composite unique constraints, specify all columns in the constraint:
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;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:
SELECT MAX(id) FROM users;
SELECT nextval('users_id_seq');If the sequence value is lower than the max ID, reset it:
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.
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:
\d+ usersLook 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.
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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL