Postgres Error: 23505 — Duplicate Key Violation

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

SQLSTATE 23505 is Postgres’s way of saying “I refused to insert this row because doing so would violate a unique constraint.” It’s the most common integrity error in production Postgres and almost always falls into one of three categories: a race between SELECT and INSERT, a non-idempotent retry, or client-generated IDs colliding under load.

The fix is rarely “lock harder” — it’s “express the operation atomically.” INSERT ... ON CONFLICT lets Postgres arbitrate concurrent writers without application-level coordination. For webhooks and event-driven flows, catching 23505 and treating it as success is the simplest possible deduplication strategy and works correctly even at high concurrency.

Why this happens

• SELECT-then-INSERT race condition. Two requests check `SELECT id FROM users WHERE email = $1`, both see no row, both run INSERT. One wins; the other gets 23505. The fix is to use a single atomic `INSERT ... ON CONFLICT` and let the database arbitrate.
• Re-running a non-idempotent migration or import. Re-running a seed script, replaying an event stream, or rerunning a migration after a partial failure all attempt to insert rows that already exist. Without idempotency built in, every retry surfaces 23505.
• Wrong column generated client-side. Generating IDs in application code (`Date.now()`, short hashes, sequence drift across replicas) eventually collides. Use UUID v4 for distributed generation, or let Postgres own the sequence with `BIGSERIAL`/`IDENTITY`.
• Composite unique constraint on a column subset. A `UNIQUE (tenant_id, slug)` constraint is satisfied by any one of those columns being different. If your app forgets to scope by tenant when checking, the duplicate-detection logic misses cross-tenant slug collisions and 23505 fires from the database.
• Concurrent UPSERTs on the same key. Even with ON CONFLICT, two concurrent inserters of the same key serialise — one gets the conflict and runs the DO UPDATE branch. If you wrote `ON CONFLICT DO NOTHING` but expected the row to be present afterwards, you may misread the empty RETURNING and treat success as failure.

How to fix it

Fixes are ordered by likelihood. Start with the first one that matches your context.

INSERT INTO users (email, name, last_login)
VALUES ($1, $2, now())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  last_login = now()
RETURNING id, email;

import { Pool } from 'pg';
const pool = new Pool();

async function getOrCreateUser(email, name) {
  const { rows: inserted } = await pool.query(
    `INSERT INTO users (email, name) VALUES ($1, $2)
     ON CONFLICT (email) DO NOTHING
     RETURNING id`,
    [email, name],
  );
  if (inserted.length) return inserted[0].id;
  const { rows: existing } = await pool.query(
    'SELECT id FROM users WHERE email = $1',
    [email],
  );
  return existing[0].id;
}

try {
  await pool.query(
    'INSERT INTO webhook_events (event_id, payload) VALUES ($1, $2)',
    [event.id, event.payload],
  );
} catch (err) {
  if (err.code === '23505') {
    // Duplicate event delivery — already processed.
    return;
  }
  throw err;
}

Detection and monitoring in production

Track 23505 separately from generic SQL errors — most are expected (idempotent retries) and should not page. Alarm only when 23505 fires on a constraint that's *meant* to be exclusive (e.g., `payments_idempotency_key_key`) and the upstream caller is supposed to be passing a unique key. Tag by constraint name in your error tracker.