How to Fix PostgreSQL Duplicate Key Value Violates Unique Constraint

The PostgreSQL error duplicate key value violates unique constraint means a write tried to create a value that a unique index or primary key already protects. PostgreSQL reports it as SQLSTATE 23505, and it is usually doing exactly what you asked the database to do: reject a second row that would make identity, email, slug, tenant-scoped name, or another business key ambiguous.

The mistake is treating every duplicate key error as an invitation to add ON CONFLICT DO NOTHING. Sometimes that is the right idempotency tool. Sometimes it hides a broken sequence, a retry bug, a missing tenant key, or an import that is about to merge the wrong customer records.

First, identify the constraint and the value

Start with the full error message. PostgreSQL normally names the constraint and includes detail such as Key (email)=([email protected]) already exists. The constraint name points to the database rule that rejected the write. If the name is not obvious, inspect it from psql with d table_name, or query the catalog for the table's constraints and indexes.

select conname, contype, pg_get_constraintdef(oid) as definition
from pg_constraint
where conrelid = 'public.users'::regclass;

For primary keys, the failure usually means the application supplied an existing ID or a sequence is behind the table. For ordinary unique constraints, the question is more domain-specific: should the value be unique globally, unique per tenant, or unique only among active rows? Answer that before changing code.

Fix sequences that drifted after imports

A common PostgreSQL variant happens after loading data with explicit IDs. The table contains rows up to ID 50000, but the sequence still thinks the next value is 42. The next insert that relies on the default value collides with an existing row.

Check the default first so you know which sequence the column uses. Then compare the maximum ID with the sequence's next value. If the sequence is behind, set it to the current maximum ID so the following nextval produces a new value.

select pg_get_serial_sequence('public.orders', 'id');

select setval(
  pg_get_serial_sequence('public.orders', 'id'),
  coalesce((select max(id) from public.orders), 1),
  true
);

The third argument to setval matters. With true, PostgreSQL treats the supplied value as already used, so the next nextval advances beyond it. Run this during a quiet migration window or inside a controlled maintenance step so another writer does not race your repair.

Use ON CONFLICT only when the conflict is expected

PostgreSQL's INSERT ... ON CONFLICT is the right tool when duplicate attempts are part of the design. A webhook handler might receive the same event twice. A background job might retry after a network failure. A sync process might upsert the latest representation of an external object.

Use DO NOTHING when the existing row is already the complete desired result. Use DO UPDATE when the new data should refresh selected columns. Name the conflict target so future readers know which uniqueness rule defines idempotency.

insert into webhook_events (provider, external_id, received_at)
values ('stripe', $1, now())
on conflict (provider, external_id) do nothing;

Do not use DO NOTHING around user-facing writes just to stop errors. If a user tries to claim an existing team slug, the product should report the conflict. Silently ignoring the insert can make the application behave as if the write succeeded when no row was created.

Recheck the data model before changing constraints

If the constraint is wrong, fix the model deliberately. A SaaS table often needs uniqueness within a tenant rather than across the whole database. That might mean changing unique (slug) to unique (tenant_id, slug). For soft-deleted rows, a partial unique index such as where deleted_at is null may express the intended rule better than a global unique constraint.

Before replacing any production constraint, find existing duplicates under the new rule and decide how to merge or rename them. The database will not create a unique index if current rows already violate it, and forcing the change without a cleanup plan turns a small application bug into a migration incident.

Application handling pattern

In application code, treat SQLSTATE 23505 as a known conflict path, not as a generic database outage. Map it to an HTTP 409 response for user-facing uniqueness conflicts, log the constraint name, and avoid exposing raw database messages to end users. For job processors, record enough context to decide whether the duplicate was successful idempotency or a true data mismatch.

The best long-term fix is usually a combination: keep the database constraint, make the application error message intentional, and use upserts only for writes that are genuinely repeatable.

Takeaway

A duplicate key error is PostgreSQL protecting a rule. Find the named constraint, decide whether the duplicate is a sequence problem, a legitimate product conflict, an idempotency case, or a modeling mistake, and then choose the narrowest fix. If you run PostgreSQL on ArmorDB, keep these checks in your migration runbook alongside backups and connection management so a routine import does not become a production incident.

Sources and further reading

• PostgreSQL error codes appendix, including SQLSTATE 23505 unique_violation: https://www.postgresql.org/docs/current/errcodes-appendix.html
• PostgreSQL documentation on unique constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
• PostgreSQL INSERT documentation for ON CONFLICT: https://www.postgresql.org/docs/current/sql-insert.html
• PostgreSQL sequence functions, including setval and nextval: https://www.postgresql.org/docs/current/functions-sequence.html