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
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.

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

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.