PostgreSQL 23505 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23505: Unique Violation — What It Is and How to Fix It

PostgreSQL error code 23505 (unique_violation) is raised when an INSERT or UPDATE statement attempts to store a value that already exists in a column (or combination of columns) constrained by a UNIQUE index or PRIMARY KEY. PostgreSQL enforces these constraints at the database level, immediately aborting the offending transaction to protect data integrity. This is one of the most frequently encountered errors in production systems, especially in high-concurrency web applications.

Top 3 Causes

1. Duplicate INSERT Without Conflict Handling

The most common cause: inserting a record without checking whether the unique value already exists. In concurrent environments, even an application-level existence check is not safe due to race conditions.

-- This will throw 23505 if 'alice@example.com' already exists
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice');

-- Safe alternative: use ON CONFLICT
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- Or UPSERT: update if exists, insert if not
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

2. Sequence Out of Sync After Data Migration

When bulk data is imported with explicit primary key values (e.g., using COPY or a migration tool), the sequence backing the SERIAL or BIGSERIAL column is not automatically advanced. The next auto-generated ID may collide with an already existing one.

-- Check the current sequence value vs. the actual max ID
SELECT last_value FROM users_id_seq;
SELECT MAX(id) FROM users;

-- Fix: reset the sequence to the current max ID
SELECT setval(
    pg_get_serial_sequence('users', 'id'),
    COALESCE((SELECT MAX(id) FROM users), 0) + 1,
    false
);

3. Batch Jobs Re-Inserting Existing Records

ETL pipelines, scheduled jobs, or retry logic often attempt to re-insert records that were already committed in a previous run. Without idempotent insert logic, every retry will trigger a 23505 error.

-- Batch UPSERT using a staging table
CREATE TEMP TABLE staging_users (
    email VARCHAR(255),
    name  VARCHAR(100)
);

-- Load batch data into staging
COPY staging_users (email, name)
FROM '/tmp/batch.csv' DELIMITER ',' CSV HEADER;

-- Merge safely into the main table
INSERT INTO users (email, name)
SELECT email, name FROM staging_users
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

DROP TABLE staging_users;

Quick Fix Solutions

Scenario	Solution
Single duplicate INSERT	Use `ON CONFLICT DO NOTHING` or `DO UPDATE`
Sequence mismatch after import	Call `setval()` with current `MAX(id)`
Batch job duplicates	Use staging table + `INSERT ... ON CONFLICT`
Composite unique violation	Specify the constraint name in `ON CONFLICT ON CONSTRAINT`

-- Handling composite unique constraint conflicts
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT,
    CONSTRAINT uq_order_product UNIQUE (order_id, product_id)
);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 42, 5)
ON CONFLICT ON CONSTRAINT uq_order_product
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;

Prevention Tips

1. Always use ON CONFLICT for any INSERT that touches a unique column.
Make INSERT ... ON CONFLICT your team's default pattern for any table with unique constraints. Never rely solely on application-level duplicate checks in concurrent systems — the database is the only safe enforcement point.

-- Partial unique index for conditional uniqueness
CREATE UNIQUE INDEX idx_active_email
ON users (email)
WHERE is_deleted = FALSE;

2. Include a sequence validation step in every data migration runbook.
After any bulk load or restore, always run a sequence sync script before reopening the application to traffic. Automate this check in your CI/CD or deployment pipeline to prevent sequence drift from causing production incidents.

-- Quick sequence health check after migration
SELECT
    sequencename,
    last_value,
    (SELECT MAX(id) FROM users) AS table_max
FROM pg_sequences
WHERE sequencename = 'users_id_seq';

Related Errors

23000 integrity_constraint_violation — Parent class of 23505; catches all constraint violations.
23502 not_null_violation — Triggered when a NULL is inserted into a NOT NULL column.
23503 foreign_key_violation — Raised when a referenced key does not exist in the parent table.
40001 serialization_failure — Often co-occurs with 23505 in high-concurrency scenarios using SERIALIZABLE isolation.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community