errorPostgreSQLError 23505

PostgreSQL Error 23505: Unique Violation

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

What is PostgreSQL Error 23505?

PostgreSQL SQLSTATE 23505 occurs when an INSERT or UPDATE violates a UNIQUE or PRIMARY KEY constraint. PostgreSQL's error message names the specific constraint and shows the conflicting key value.

Common Causes

  • 1

    Inserting a row where a unique-indexed column already has that value

  • 2

    Concurrent inserts from multiple transactions both trying to insert the same unique value

  • 3

    Sequence gaps causing ID conflicts in manual ID assignment

  • 4

    Data migration inserting records that already exist

Step-by-Step Solutions

1

Use INSERT ... ON CONFLICT DO NOTHING: INSERT INTO users (email) VALUES ('x@x.com') ON CONFLICT (email) DO NOTHING;

2

Use INSERT ... ON CONFLICT DO UPDATE (upsert): INSERT INTO users (email, name) VALUES ('x@x.com', 'New') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

3

Check existence first: INSERT INTO t SELECT v WHERE NOT EXISTS (SELECT 1 FROM t WHERE col = v)

4

For sequences: SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

Prevention Tips

  • Use ON CONFLICT for all INSERT operations on tables with unique constraints

  • Use database-generated IDs (SERIAL or GENERATED ALWAYS AS IDENTITY) instead of application-assigned IDs

  • Implement optimistic locking for concurrent update scenarios

  • Pre-validate uniqueness in the application layer for better user feedback (but still handle DB constraint)

Frequently Asked Questions

How do I do an upsert in PostgreSQL?
Use INSERT ... ON CONFLICT: INSERT INTO users (id, email, name) VALUES (1, 'a@b.com', 'Alice') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, name = EXCLUDED.name; EXCLUDED refers to the row that was proposed for insertion. This is PostgreSQL's upsert syntax, equivalent to MySQL's ON DUPLICATE KEY UPDATE.
How do I insert if not exists in PostgreSQL?
INSERT INTO table (col) VALUES (val) ON CONFLICT (col) DO NOTHING; This silently skips the insert if a conflict is detected on the specified column. The DO NOTHING clause means the conflicting row in the database remains unchanged.

Related Errors

POSTGRESQL 23503POSTGRESQL 23502POSTGRESQL 23514

Still Stuck?

Ask our AI SQL Assistant or the community — get answers in seconds.

Ask AI Assistant Test in Playground