PostgreSQL Error 23505: Unique violation

Identify: does your log match?

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

1. High

   Check-then-insert race condition

   Two requests both SELECT, see no row, then both INSERT. The second one hits the unique index.

2. Medium

   Sequence out of sync after import

   Rows were bulk-loaded with explicit ids, so the serial sequence still returns ids that already exist. Every new INSERT collides on the primary key.

3. Medium

   Retried request without idempotency

   A client or queue retried a successful INSERT after a timeout. The original write already committed.

Fix

SELECT id FROM users WHERE email = $1;
-- app code: if no row, then...
INSERT INTO users (email, name) VALUES ($1, $2);

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

Verify: reproduce it on purpose

$ psql -c "CREATE TEMP TABLE u(email text UNIQUE); INSERT INTO u VALUES ('a@x.io'),('a@x.io');"

Prevent

• Enforce uniqueness in the database, never only in app code.
• Map 23505 to HTTP 409 with the constraint name from err.constraint.
• Send an Idempotency-Key on retried POSTs.