PostgreSQL 23000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 23000: Integrity Constraint Violation

PostgreSQL error code 23000 (integrity_constraint_violation) is a parent-class error that occurs when an operation attempts to violate a constraint defined on a table or column. This includes violations of foreign keys, unique constraints, NOT NULL rules, and CHECK conditions. In practice, this error (along with its more specific subclass codes like 23503 and 23505) is one of the most common errors developers encounter when working with relational data.

Top 3 Causes

1. Foreign Key Violation (23503)

This happens when you try to insert a row that references a non-existent parent record, or delete a parent record that still has child rows referencing it.

-- Attempting to insert an order with a non-existent customer
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1001, 9999, 500.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- Fix: Check parent existence first, or use ON DELETE CASCADE
ALTER TABLE orders
    DROP CONSTRAINT orders_customer_id_fkey;

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE;

2. Unique Constraint Violation (23505)

Occurs when inserting or updating a row with a value that already exists in a column defined with a UNIQUE or PRIMARY KEY constraint.

-- Duplicate email insert attempt
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

-- Fix: Use ON CONFLICT for upsert behavior
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = NOW();

-- Or simply ignore duplicates
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email) DO NOTHING;

3. CHECK or NOT NULL Constraint Violation (23514 / 23502)

Triggered when inserting a NULL into a NOT NULL column, or when data fails a CHECK constraint rule defined on the table.

-- Inserting a negative price when CHECK (price > 0) is defined
INSERT INTO products (name, price)
VALUES ('Widget', -100);
-- ERROR: new row for relation "products" violates check constraint

-- Inspect existing constraints before inserting
SELECT
    conname AS constraint_name,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass
  AND contype IN ('c', 'n');

-- Fix: Validate data before insert, or update the constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;
ALTER TABLE products ADD CONSTRAINT products_price_check CHECK (price >= 0);

Quick Fix Solutions

• Always check parent records exist before inserting child rows with foreign key references.
• Use ON CONFLICT clauses for any insert operations that may encounter duplicate keys.
• Query pg_constraint to audit all active constraints on a table before bulk data loads.
• Wrap risky operations in transactions so you can roll back cleanly on any violation.

-- Safe transactional pattern
BEGIN;
    -- Validate and insert parent
    INSERT INTO customers (customer_id, name)
    VALUES (9999, 'New Customer')
    ON CONFLICT (customer_id) DO NOTHING;

    -- Now safely insert child
    INSERT INTO orders (order_id, customer_id, amount)
    VALUES (1001, 9999, 500.00);
COMMIT;

Prevention Tips

1. Pre-validate data before bulk loads. Run a staging query to detect violations before committing large data sets to production tables.

-- Pre-flight check before bulk insert
SELECT s.customer_id, 'Missing FK reference' AS issue
FROM orders_staging s
LEFT JOIN customers c ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

2. Implement dual-layer validation. Enforce constraints at both the application layer and the database layer. The database constraint is your last line of defense — your application should catch bad data before it ever reaches PostgreSQL. Use migration tools (Flyway, Liquibase) to version-control all constraint changes and ensure they are reviewed before hitting production.

Related Error Codes

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