PostgreSQL 23000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23000: Integrity Constraint Violation

PostgreSQL error code 23000 is a top-level error class representing an integrity constraint violation, thrown whenever an operation attempts to insert, update, or delete data that breaks a rule defined on the database schema. In practice, you will almost always see a more specific child error code (such as 23503 or 23505) alongside it, but understanding the parent class is essential for robust error handling in your application.

Top 3 Causes

1. Foreign Key Violation (23503)

Inserting a child record that references a non-existent parent, or deleting a parent record that still has dependent children, triggers this error. It is the single most common cause in production systems, especially during bulk data migrations where insertion order is wrong.

-- This fails if customer_id 999 does not exist in customers table
INSERT INTO orders (customer_id, product, amount)
VALUES (999, 'Laptop', 1500.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

-- Fix: Insert parent record first, then the child
BEGIN;
  INSERT INTO customers (id, name) VALUES (999, 'John Doe')
  ON CONFLICT (id) DO NOTHING;

  INSERT INTO orders (customer_id, product, amount)
  VALUES (999, 'Laptop', 1500.00);
COMMIT;

-- Find all orphaned child records
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

2. Unique Constraint Violation (23505)

Attempting to insert a duplicate value into a column with a UNIQUE or PRIMARY KEY constraint causes this error. It is especially problematic in high-concurrency environments where race conditions can cause two transactions to attempt the same insert simultaneously.

-- This fails if the email already exists
INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

-- Fix: Use INSERT ... ON CONFLICT (Upsert)
INSERT INTO users (email, username, updated_at)
VALUES ('alice@example.com', 'alice_new', NOW())
ON CONFLICT (email)
DO UPDATE SET
  username = EXCLUDED.username,
  updated_at = EXCLUDED.updated_at;

-- Find all duplicate emails before enforcing a unique constraint
SELECT email, COUNT(*) AS duplicates
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicates DESC;

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

Inserting a NULL into a NOT NULL column, or providing a value that fails a CHECK expression, raises these errors. They commonly appear when migrating data from legacy systems with poor data quality or when application-level validation is skipped.

-- NOT NULL violation
INSERT INTO employees (id, name, department_id)
VALUES (1, 'Bob', NULL);
-- ERROR: null value in column "department_id" violates not-null constraint

-- CHECK violation
INSERT INTO products (name, price)
VALUES ('Widget', -50.00);
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"

-- Fix: Use COALESCE and GREATEST to sanitize input
INSERT INTO employees (id, name, department_id, salary)
VALUES (
  1,
  'Bob',
  COALESCE(NULL, 1),         -- fallback to default department
  GREATEST(COALESCE(NULL, 0), 0)  -- ensure non-negative salary
);

-- Inspect existing CHECK constraints on a table
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass
  AND contype = 'c';

Quick Fix Solutions

-- Temporarily disable triggers for bulk migration (re-enable immediately after!)
ALTER TABLE orders DISABLE TRIGGER ALL;
-- ... bulk load data ...
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Validate a constraint after fixing data
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

-- Catch constraint violations gracefully in PL/pgSQL
DO $$
BEGIN
  INSERT INTO orders (customer_id, product, amount)
  VALUES (999, 'Laptop', 1500.00);
EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE NOTICE 'Parent record does not exist. Skipping insert.';
  WHEN unique_violation THEN
    RAISE NOTICE 'Duplicate record detected. Skipping insert.';
  WHEN integrity_constraint_violation THEN
    RAISE NOTICE 'Generic constraint violation caught.';
END;
$$;

Prevention Tips

1. Always validate data before it hits the database.
Apply input validation at the application layer and use ON CONFLICT clauses for idempotent inserts. Never rely solely on the database to catch bad data — catching it earlier is cheaper and produces better user-facing error messages.

2. Monitor and log constraint violations proactively.
Configure log_min_error_statement = 'error' in postgresql.conf and integrate with tools like pgBadger or Prometheus to detect recurring violation patterns before they become incidents. Set up alerts when 23xxx error codes spike in your log pipeline.

Related Error Codes

Code	Name	Description
23001	restrict_violation	RESTRICT rule triggered on delete/update
23502	not_null_violation	NULL inserted into NOT NULL column
23503	foreign_key_violation	Referenced parent key does not exist
23505	unique_violation	Duplicate value in UNIQUE/PK column
23514	check_violation	Value fails a CHECK constraint
23P01	exclusion_violation	EXCLUDE constraint conflict (e.g., overlapping ranges)

📖 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