PostgreSQL Duplicate Key Violates Unique Constraint: Fix Error 23505

By Sharon Ben-Moshe · September 5, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Troubleshoot and fix PostgreSQL Error 23505: duplicate key value violates unique constraint. Learn how to fix desynced sequences and use ON CONFLICT.

Quick answer

PostgreSQL error 23505 occurs when an INSERT or UPDATE violates a UNIQUE or PRIMARY KEY constraint. If caused by manually inserted IDs desyncing an auto-increment sequence, fix it by advancing the sequence with SELECT setval(pg_get_serial_sequence('table', 'id'), max(id)) FROM table. To handle application-level duplicates gracefully, use INSERT ... ON CONFLICT DO NOTHING or DO UPDATE.

FAQs

What is SQLSTATE 23505 in PostgreSQL?

SQLSTATE 23505 represents unique_violation. PostgreSQL throws it whenever an operation attempts to create a row whose indexed columns match an existing row under a UNIQUE index or PRIMARY KEY constraint.

Why does an auto-increment sequence produce duplicate key errors?

Sequences do not check the table for existing maximum values. If rows were inserted with explicit ID values (e.g. during a migration or database restore), the sequence counter remains behind the maximum ID in the table. The next nextval() call will collide with an existing row.

How do I ignore duplicate key errors on insert?

Add `ON CONFLICT (constraint_columns) DO NOTHING` to your INSERT statement. PostgreSQL will skip conflicting rows silently without aborting the transaction.

Can ON CONFLICT update the existing row instead of failing?

Yes. Use `ON CONFLICT (constraint_columns) DO UPDATE SET col = EXCLUDED.col`. This is known as an UPSERT and guarantees atomic updates without concurrency race conditions.

SQL Errors

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

By Sharon Ben-Moshe · Sep 29, 2026

Fix Google BigQuery's "Unrecognized name: column" error. Learn alias visibility rules across WHERE and QUALIFY, case sensitivity, and struct unnesting.

Read post

SQL Errors

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

By Sharon Ben-Moshe · Sep 26, 2026

Understand SQLite Error 20 (datatype mismatch). Learn how SQLite type affinity, CHECK constraints, and STRICT tables enforce data integrity.

Read post

SQL Errors

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Resync primary key sequences after bulk imports using setval() with pg_get_serial_sequence().

Use INSERT ... ON CONFLICT (id) DO NOTHING to skip records that already exist.

Use EXCLUDED pseudo-record in ON CONFLICT DO UPDATE to perform atomic upserts.

Never query MAX(id) + 1 in application code to assign IDs; rely on Postgres sequences or UUIDs.

The primary culprit: desynced sequences after data imports

When importing data via CSV, pg_dump, or test seeders with explicit primary key IDs, PostgreSQL's sequence generator does not automatically advance. The next time nextval() is called, it returns an ID that already exists in the table.

Diagnosing and fixing an out-of-sync sequence

-- 1. Check current sequence value vs actual table maximum
SELECT currval(pg_get_serial_sequence('users', 'id'));
SELECT MAX(id) FROM users;

-- 2. Resynchronize the sequence to the highest existing ID
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) + 1 FROM users), 1),
  false
);

Handling application duplicate conflicts gracefully with UPSERT

When user actions or webhook retries can generate duplicate rows on unique email addresses or external IDs, use ON CONFLICT to guarantee idempotency.

Idempotent insert with ON CONFLICT DO UPDATE

INSERT INTO user_profiles (user_id, email, full_name, updated_at)
VALUES (42, 'alex@example.com', 'Alex Rivera', NOW())
ON CONFLICT (email)
DO UPDATE SET
  full_name = EXCLUDED.full_name,
  updated_at = NOW();

Common mistakes to avoid

Using SELECT before INSERT in application code: this creates a race condition where two simultaneous requests pass the check and crash on insert.

Targeting the wrong unique constraint in ON CONFLICT: the expression in parentheses must match an existing UNIQUE index definition exactly.

Forgetting to update sequences in staging or development after applying production seed snapshots.

Quick answer

FAQs

What is SQLSTATE 23505 in PostgreSQL?

Why does an auto-increment sequence produce duplicate key errors?

How do I ignore duplicate key errors on insert?

Can ON CONFLICT update the existing row instead of failing?

Keep reading

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

Key takeaways

The primary culprit: desynced sequences after data imports

Handling application duplicate conflicts gracefully with UPSERT

Common mistakes to avoid

Related SQL guides

Official references