PostgreSQL "duplicate key value violates unique constraint" — Why Sequences Drift

Root cause of duplicate key violations after restore or bulk migration, one-line setval repair, and an automated schema-wide PL/pgSQL DO block.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: PostgreSQL 14.10, 15.6, 16.4, 17.0 (Ubuntu 24.04 LTS / Docker 26.1)

Operational Verification & Reliability Metadata

Last Technical Review: 27 Ekim 2026

Tested VersionsPostgreSQL 14.10, 15.6, 16.4, 17.0 (Ubuntu 24.04 LTS, Docker 26.1, Kernel 6.8)

Required PrivilegesSUPERUSER or pg_read_all_stats / pg_signal_backend

Risk Assessment

MediumTerminating active sessions forces application-layer transaction rollbacks

Expected OutputTotal connections drop safely below max_connections threshold in pg_stat_activity

Rollback Procedure:Reset temporary session timeouts, reload PgBouncer or revert pool sizing

PostgreSQL Docs: Connections

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1043) already exists.

Occurs when a sequence is not advanced following a restore or explicit primary key insert.

▸Single Table Repair

sql

SELECT setval(
  pg_get_serial_sequence('public.users', 'id'),
  (SELECT max(id) FROM public.users)
);

•PostgreSQL First Restore Drill Runbook
•PostgreSQL Backup & Restore CheatSheet
•Validated versions: PostgreSQL 14 / 15 / 16 / 17

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (20)

POSTGRESQL5 min

PostgreSQL too many clients already: 6-Step Resolution

Three root causes of FATAL: sorry, too many clients already, single diagnostic query to identify the cause, and permanent mitigation via max_connections and PgBouncer.

POSTGRESQL4 min

PostgreSQL password authentication failed: Root Cause and Fix

Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.

POSTGRESQL4 min

PostgreSQL connection refused: 8-Step Definitive Triage

Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.