PostgreSQL 23505 — Unique Violation Error

PostgreSQL Error 23505: unique_violation

Error 23505 occurs when an INSERT or UPDATE would create a duplicate value in a column or combination of columns that has a UNIQUE constraint or is a PRIMARY KEY.

Error Format

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(user@example.com) already exists.

Handling with Upsert (INSERT ... ON CONFLICT)

-- Update if exists, insert if not
INSERT INTO users (email, name, updated_at)
VALUES ('user@example.com', 'John', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

-- Insert and ignore duplicates
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

Common Scenarios

1. Race condition in concurrent inserts: Two requests simultaneously check for existence, both find nothing, then both try to insert.

-- Bad: check-then-insert pattern (race condition)
-- Better: use ON CONFLICT or advisory locks

-- Advisory lock approach
SELECT pg_advisory_xact_lock(hashtext('user@example.com'));
INSERT INTO users (email) VALUES ('user@example.com')
ON CONFLICT DO NOTHING;

2. Sequence gaps after failed inserts: Serial/IDENTITY columns increment even on failed inserts. This is normal and expected. Do not rely on sequences being gap-free.

3. Partial unique indexes:

-- Unique email only for active users
CREATE UNIQUE INDEX users_email_active
ON users (email) WHERE deleted_at IS NULL;

Application-Level Handling

// Node.js with pg
try {
  await pool.query(
    'INSERT INTO users (email) VALUES ($1)',
    ['user@example.com']
  );
} catch (err) {
  if (err.code === '23505') {
    // Handle duplicate - update or return existing
  } else {
    throw err;
  }
}

Best Practices

1. Design unique constraints to reflect business rules
2. Use ON CONFLICT for idempotent operations
3. Handle 23505 in application code for user-friendly error messages
4. Consider partial unique indexes for soft-delete patterns
5. Do not assume gap-free sequences