/errors/postgresql/duplicate-key-violates-unique-constraint
PostgreSQL: duplicate key value violates unique constraint
- tool
- PostgreSQL
- exit code
- 0
- versions
- PostgreSQL 12 – 18
- os
- any
PostgreSQL rejects an INSERT or UPDATE that would put a value into a primary key or unique column that already holds it: ERROR: duplicate key value violates unique constraint "users_pkey" with SQLSTATE 23505. The DETAIL line names the column and the value, for example Key (id)=(1) already exists.
Reproduce it
PostgreSQL 18 in-process with PGlite; the second and third inserts collide on the primary key and on the unique email:
npm init -y >/dev/null
npm install --silent --no-audit --no-fund @electric-sql/pglite@0.5.10 >/dev/null 2>&1
cat > q.mjs <<'EOF'
import { PGlite } from "@electric-sql/pglite";
const db = new PGlite();
const run = async (sql) => {
try { await db.query(sql); console.log("OK ", sql); }
catch (e) { console.log(`ERROR: ${e.message}\nDETAIL: ${e.detail}\nSQLSTATE ${e.code}`); }
};
await run(`create table users (id int primary key, email text unique)`);
await run(`insert into users values (1, 'a@example.com')`);
await run(`insert into users values (1, 'b@example.com')`);
await run(`insert into users values (2, 'a@example.com')`);
EOF
node q.mjsOutput
OK create table users (id int primary key, email text unique) OK insert into users values (1, 'a@example.com') ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(1) already exists. SQLSTATE 23505 ERROR: duplicate key value violates unique constraint "users_email_key" DETAIL: Key (email)=(a@example.com) already exists. SQLSTATE 23505
Fix: the sequence is behind
After loading rows with explicit ids, a serial or identity column keeps handing out numbers that already exist. Move the sequence past the maximum:
SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users));Fix: duplicates are expected
INSERT INTO users (id, email) VALUES (1, 'b@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
INSERT INTO users (id, email) VALUES (1, 'b@example.com')
ON CONFLICT DO NOTHING;In application code, catch SQLSTATE 23505 (not the message text, which can be translated) and map it to a "already exists" response.