ERROR 23505: Duplicate Key Value Violates Unique Constraint in Postgres

Error Message#

ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(alice@example.com) already exists.
SQLSTATE: 23505

The error message names the constraint that rejected the write and a DETAIL line listing the column(s) and the conflicting value(s). For composite unique constraints you'll see every column:

The underlying SQLSTATE is always 23505 (unique_violation), regardless of whether the conflict is on a primary key, a UNIQUE constraint, or a unique index.

What Triggers This Error#

23505 fires whenever PostgreSQL refuses an INSERT or UPDATE that would create a row whose value(s) on a unique-indexed column(s) match an existing row. The most common triggers:

• Sequence drift after pg_restore or COPY — the table's serial/identity sequence keeps its old last_value while the imported data already occupies higher IDs; the next INSERT collides on its first attempt
• Non-atomic "find or create" code paths — Rails find_or_create_by, Sequelize findOrCreate, or any hand-rolled SELECT then INSERT races under concurrency. (Django get_or_create and modern Prisma upsert are atomic when the lookup field has a unique constraint.)
• Webhook or payment retries reusing the same idempotency key — Stripe webhooks, payment processors, and event buses retry on 5xx and timeouts; the same idempotency value arrives twice
• Composite unique constraint that application logic misses — code checks email is unique but the constraint is on (email, tenant_id), or vice-versa
• Logical replication subscriber receives a pre-existing row — initial-load timing or DDL drift makes the subscriber try to apply an INSERT that's already there
• Concurrent INSERTs after an existence check — two requests both read "no row with this slug" and then both try to insert; the second loses to 23505
• Migration adds UNIQUE to a column with existing duplicates — production data violates the new constraint before the index can be built

Fix by Scenario#

Sequence drift after pg_restore or COPY#

After bulk-importing data, the underlying serial or IDENTITY sequence keeps its old last_value. The next INSERT asks the sequence for its smallest available value, gets one that's already in the table, and 23505 fires.

Reset the sequence to one above the existing max:

-- For SERIAL / OWNED-BY sequences
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 0) + 1,
  false
);

-- For IDENTITY columns (Postgres 10+)
SELECT MAX(id) FROM users;  -- e.g., 5023
ALTER TABLE users ALTER COLUMN id RESTART WITH 5024;

Do this for every table after every restore. Better, use a restore script that loops over information_schema.sequences and resets each one automatically — running it manually once a quarter doesn't work.

ORM "find or create" races under concurrency#

A naive read-then-insert pattern — SELECT WHERE ... → if no row, INSERT — races under concurrent load. Two requests both read "no match", both try to insert, and PostgreSQL rejects the second with 23505.

Some ORMs handle this transparently when the lookup field has a unique constraint: Django's get_or_create() catches the resulting IntegrityError and retries the lookup, and modern Prisma compiles upsert() down to a native INSERT ... ON CONFLICT for eligible queries. Both have gotchas — Django still races when the lookup field isn't backed by a unique index, and Prisma falls back to a non-atomic path for shapes its native upsert doesn't cover.

The patterns that genuinely race in 2026:

• Rails find_or_create_by — explicitly documented as non-atomic; the Rails guidance is to add a unique index and rescue ActiveRecord::RecordNotUnique, or to use upsert_all
• Sequelize findOrCreate — runs SELECT then INSERT in separate statements
• Hand-rolled "if not exists, insert" code paths in any language

# WRONG — races under concurrency
User.find_or_create_by(email: params[:email]) do |u|
  u.name = params[:name]
end
# ActiveRecord::RecordNotUnique: duplicate key value violates unique constraint

The fix is to push uniqueness into a single statement with INSERT ... ON CONFLICT:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING
RETURNING id;

For upsert semantics (insert or update):

INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', now())
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at
RETURNING id;

When you do need ORM-side helpers, prefer the ones that emit ON CONFLICT directly: Rails 6+ upsert_all, Django 4+ bulk_create(update_conflicts=True), Prisma createMany({ skipDuplicates: true }), SQLAlchemy Insert.on_conflict_do_update.

Idempotency-key collision from retries#

Webhook providers and payment processors retry on 5xx (and sometimes on timeouts even when the original request succeeded). If you store the idempotency key in a UNIQUE column, retries are supposed to hit 23505 — your job is to handle it as success, not as an error:

INSERT INTO webhook_events (idempotency_key, payload, received_at)
VALUES ($1, $2, now())
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;

If the RETURNING is empty, the row was a duplicate retry — return 200 to the caller, don't 500 and don't trigger PagerDuty. If you have downstream side effects (sending email, writing to another system), don't perform them inside the database transaction. Instead, write an outbox/enqueue record in the same transaction as the INSERT and process it after commit, or trigger the side effect only after the INSERT transaction commits successfully, so the side effects only run when the INSERT actually creates a row.

Composite unique-key collision#

The error message names the constraint and the conflicting columns. If you see Key (email, tenant_id)=(...) but your application code only checks email for uniqueness, the constraint and the code disagree:

-- The constraint
CREATE UNIQUE INDEX users_email_tenant_key ON users (email, tenant_id);

Either fix the application logic to check the same compound key, or — if the multi-column scope was unintentional — replace the constraint with a single-column one:

DROP INDEX users_email_tenant_key;
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

Before swapping, confirm there are no legitimate cases where the same email belongs to two tenants. If there are, you can't drop the composite — the application logic was wrong and needs to learn about the tenant dimension.

Logical replication subscriber receives a pre-existing row#

On the subscriber, you'll see 23505 from the apply worker, often crash-looping. Common causes:

• Initial table copy and streaming replication overlapped, and a row was both copied and replicated
• A row was inserted directly on the subscriber and then the publisher also inserts the same primary key
• DDL on the publisher (a TRUNCATE followed by INSERT) wasn't replicated, leaving stale data on the subscriber

Check pg_stat_subscription for the failing LSN, then either delete the conflicting row on the subscriber (if the publisher's version is canonical) or skip the LSN:

ALTER SUBSCRIPTION my_sub SKIP (lsn = '0/12345678');

After PostgreSQL 15 you can set disable_on_error = true on the subscription so the apply worker doesn't crash-loop on a single bad row:

ALTER SUBSCRIPTION my_sub SET (disable_on_error = true);

Concurrent INSERTs after an existence check#

Application-level "check then insert" without locking is the classic race that produces 23505:

# WRONG — races under concurrency
existing = db.query(User).filter_by(email=email).first()
if not existing:
    db.add(User(email=email, name=name))
    db.commit()  # 23505 here under load

Two concurrent requests both run the SELECT, both see no match, both attempt the INSERT — the second loses. The fix is INSERT ... ON CONFLICT, not a retry loop:

INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email) DO NOTHING
RETURNING id;

Retry loops just hide the contention. Atomic upsert eliminates it.

Migration adds UNIQUE to a column with existing duplicates#

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email) fails if any duplicates exist:

Find the duplicates first:

SELECT email, COUNT(*) AS n, MIN(id) AS keep_id, ARRAY_AGG(id ORDER BY id) AS all_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

Decide whether to merge rows (preserve the oldest, redirect foreign keys to it), drop the newer ones, or expand the constraint to (email, tenant_id) if the duplicates are legitimate. Once duplicates are resolved, re-run the migration.

For large production tables, build the index without holding a long lock:

-- Build concurrently (no lock), then promote to a constraint
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_key;

This avoids the long blocking lock that a direct ADD CONSTRAINT UNIQUE can take while it builds and scans the unique index.

Prevention#

• Push uniqueness into a single statement with INSERT ... ON CONFLICT. Application-level "check then insert" loses to concurrency every time.
• After any pg_restore or COPY-style bulk load, reset every owned sequence to MAX(column) + 1. Bake this into the restore script.
• For idempotency keys, treat 23505 as the success path for retries — don't log it as an error or trigger alerts.
• When adding a UNIQUE constraint to an existing table, run a duplicate-finder query first and resolve hits before the migration ships.
• Make composite vs single-column uniqueness an explicit decision documented in the schema, not an emergent property of which constraint you wrote last.
• For logical replication, set disable_on_error = true on subscriptions so apply workers don't crash-loop on a single bad row.