PostgreSQL Error 23505: Unique Violation

PostgreSQL error 23505 (Unique Violation) explained: what it means, why it happens, and how to fix it — with copy-paste code examples.

SQLSTATE 23505 PostgreSQL Last verified 2026-08-19

Quick Answer

Inspect the error detail for the exact constraint and conflicting key value. If that does not apply, decide between rejecting, updating, or skipping the duplicate row — the full checklist is below.

Error Code

SQLSTATE: 23505
Official name: Unique Violation
System name: unique_violation
Service: PostgreSQL

What does this error mean?

A unique constraint or exclusion constraint was violated while inserting or updating a row, because a conflicting row already exists.

Exact Error Messages

You may see this error reported as one of the following messages:

Common Causes

How to Fix

  1. Inspect the error detail for the exact constraint and conflicting key value
  2. Decide between rejecting, updating, or skipping the duplicate row
  3. Use INSERT ... ON CONFLICT (column) DO UPDATE SET ... to upsert safely
  4. If using a serial/identity column, fix the sequence with setval() to match MAX(id)
  5. Add application-level de-duplication or a unique-index-aware retry strategy

Example Scenario

A users table has UNIQUE(email) and a sequence-backed id. On signup, the app runs INSERT INTO users (email, name) VALUES ('john@example.com', 'John') — but that email already exists from an earlier signup that was soft-deleted. PostgreSQL aborts the statement with SQLSTATE 23505 and detail: Key (email)=(john@example.com) already exists. The same error appears when a row was inserted with an explicit id (say 500) above the sequence position, so the next auto-generated id collides.

Diagnostic Commands

Run these to confirm the cause before applying a fix — each command answers a specific question.

Which constraint fired, and on which columns? sql 
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'u';

Returns the exact unique constraint name and column list — match it against the key named in the error detail.

Which existing row conflicts with my insert? sql 
SELECT id, email, created_at FROM users
WHERE email = 'john@example.com';

The conflicting row is usually a soft-deleted or duplicated account — decide whether to reactivate it or reject the insert.

Is my sequence behind MAX(id)? sql 
SELECT last_value, (SELECT MAX(id) FROM users) AS max_id
FROM users_id_seq;

If last_value < max_id, an explicit-id insert desynchronized the sequence; fix it with setval().

Decision Guide

Duplicate value is intentional (upsert)?
├─ YES → use INSERT ... ON CONFLICT (email) DO UPDATE
│        (atomic, no race window)
└─ NO → find the conflicting row
         ├─ existing row is valid → fix/reject the application input
         └─ existing row is stale/duplicate → clean the data, then retry

Duplicate is the auto-generated id itself?
└─ sequence out of sync → SELECT setval('users_id_seq',
             COALESCE(MAX(id),0)+1, false) FROM users;

Code Examples

Upsert with ON CONFLICT sql 
INSERT INTO users (email, name)
VALUES ('a@b.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id;

ON CONFLICT converts a would-be unique violation into an atomic update, avoiding error 23505 entirely.

Fix an out-of-sync sequence sql 
SELECT setval('users_id_seq', COALESCE(MAX(id), 0) + 1, false)
FROM users;

When a manually-set id exceeds the sequence value, the next auto-generated id collides; reset the sequence above MAX(id).

Framework-Specific Fixes

prisma

Catch PrismaClientKnownRequestError with code P2002 (maps to SQLSTATE 23505) and return a clean duplicate response instead of crashing.

try {
  await prisma.user.create({ data: { email } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2002') {
    return { error: 'Email already exists' };
  }
  throw e;
}

django

Use get_or_create() or catch IntegrityError (django.db) to handle duplicates at the ORM layer.

from django.db import IntegrityError

try:
    obj = MyModel.objects.create(email=email)
except IntegrityError:
    obj = MyModel.objects.get(email=email)

sqlalchemy

Catch sqlalchemy.exc.IntegrityError, optionally with savepoint rollback before retrying.

from sqlalchemy.exc import IntegrityError

try:
    session.add(User(email=email))
    session.commit()
except IntegrityError:
    session.rollback()

supabase

Use upsert() with onConflict to let Postgres handle duplicates server-side instead of returning 23505.

const { error } = await supabase
  .from('users')
  .upsert({ email }, { onConflict: 'email' })

You Might Also Like

23503 Foreign Key Violation A foreign key constraint was violated because a referenced row does not exist, or an operation would orphan… 23502 Not Null Violation A NOT NULL constraint was violated by inserting or updating a column with a NULL value. MySQL1062 Duplicate Entry Duplicate entry '%s' for key %s PrismaP2002 Unique Constraint Failed Unique constraint failed on the {constraint}

Frequently Asked Questions

Why am I seeing PostgreSQL error 23505?

Most often this happens when inserting a duplicate value into a column with a UNIQUE constraint, or when using INSERT ... ON CONFLICT DO NOTHING but the conflict clause omits the right column.

How do I fix PostgreSQL error 23505?

Inspect the error detail for the exact constraint and conflicting key value.

Which frameworks have documented fixes for error 23505?

This page documents fixes for: prisma, django, sqlalchemy, supabase.

Official Sources

This page is based on the official PostgreSQL documentation linked below and adds practical troubleshooting guidance on top.