PostgreSQL Error 23505 - Unique Violation

What It Means

PostgreSQL error code 23505 (unique_violation) occurs when an INSERT or UPDATE statement attempts to store a value that would violate a unique constraint or unique index. This means a row with the same value(s) already exists in the column(s) covered by the constraint.

Common Causes

• Inserting a duplicate primary key value
• Inserting a duplicate value in a column with a UNIQUE constraint
• Concurrent inserts creating race conditions
• Application retrying an insert that already succeeded
• Bulk data imports with duplicate rows
• Sequence/serial counter out of sync after manual data insertion

How to Fix

Use ON CONFLICT (upsert)

-- Insert or update on conflict
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Insert or do nothing on conflict
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email) DO NOTHING;

-- With composite unique constraint
INSERT INTO user_roles (user_id, role_id)
VALUES (1, 2)
ON CONFLICT (user_id, role_id) DO NOTHING;

Handle in application code

// Node.js with pg
const { Pool } = require('pg');
const pool = new Pool();

async function createUser(email, name) {
  try {
    const result = await pool.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
      [email, name]
    );
    return result.rows[0];
  } catch (error) {
    if (error.code === '23505') {
      throw new Error(`User with email ${email} already exists`);
    }
    throw error;
  }
}

# Python with psycopg2
import psycopg2

try:
    cursor.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        (email, name)
    )
    conn.commit()
except psycopg2.errors.UniqueViolation:
    conn.rollback()
    print(f"User with email {email} already exists")

Fix sequence out of sync

-- If serial/sequence is behind the actual data (common after imports)
SELECT MAX(id) FROM users;  -- Check the highest ID

-- Reset the sequence
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- Or set to a specific value
SELECT setval('users_id_seq', 1000, true);

Find duplicates

-- Find duplicate values in a column
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find which constraint was violated (from error message)
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

Related Errors

• PostgreSQL 23503 - Foreign key violation: Referencing a non-existent parent row.
• PostgreSQL 42P01 - Undefined table: The table itself doesn’t exist.
• MySQL 1062 - Duplicate entry: The MySQL equivalent of this error.