# ERROR 23505: Duplicate Key Value Violates Unique Constraint in Postgres

Source: https://www.bytebase.com/reference/postgres/error/23505-duplicate-key-value/

---

## Error Message

```sql
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:

```
ERROR: duplicate key value violates unique constraint "subscriptions_user_plan_key"
DETAIL: Key (user_id, plan_id)=(42, premium) already exists.
```

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:

```sql
-- 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

```ruby
# 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`:

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

For upsert semantics (insert or update):

```sql
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:

```sql
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:

```sql
-- 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:

```sql
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:

```sql
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:

```sql
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:

```python
# 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:

```sql
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:

```
ERROR: could not create unique index "users_email_key"
DETAIL: Key (email)=(alice@example.com) is duplicated.
```

Find the duplicates first:

```sql
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:

```sql
-- 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.

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) flags `ADD CONSTRAINT UNIQUE` migrations against tables with existing duplicate values before the change runs, catching the most common 23505 source during change review. See also [ERROR 23503: Foreign Key Violation](/reference/postgres/error/23503-foreign-key-violation) and [ERROR 22001: String Data Right Truncation](/reference/postgres/error/22001-string-data-right-truncation) — both are integrity-constraint siblings of 23505.