PostgreSQL aborts transactions when data violates constraints like unique keys, foreign keys, or CHECK rules. Learn to identify which constraint failed, fix data conflicts, and use deferrable constraints to prevent transaction rollbacks.
A transaction integrity constraint violation occurs when an INSERT, UPDATE, or DELETE operation attempts to violate a database constraint. PostgreSQL has strict transactional semantics: when any constraint is violated, the entire transaction is automatically aborted and rolled back. This is different from some databases that allow partial failure recovery. The error includes details about which constraint was violated (e.g., unique constraint, foreign key, or check constraint) and the specific values that caused the conflict. This behavior ensures data consistency but requires applications to handle constraint violations gracefully. The most common causes are duplicate primary/unique keys, foreign key references to non-existent records, or values outside CHECK constraint ranges.
PostgreSQL includes helpful information in the error message:
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=([email protected]) already exists.or
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(999) is not present in table "customers".Identify whether it's a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint violation. The constraint name helps you locate the definition in your schema.
If the error is about UNIQUE or PRIMARY KEY:
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;This query finds duplicate email addresses. Either:
- Delete/merge duplicate rows if they shouldn't exist
- Use a different value for the current INSERT if this is a new record
- Check application logic for why duplicates are being generated
If the error involves a foreign key constraint:
-- Check if referenced parent record exists
SELECT * FROM customers WHERE id = 999;
-- If not found, either:
-- 1. Create the parent record first
INSERT INTO customers (id, name) VALUES (999, 'New Customer');
-- 2. Or use an existing parent ID
INSERT INTO orders (customer_id, amount) VALUES (1, 100);Always insert parent records before child records within a transaction.
For complex transactions that must violate constraints temporarily, define constraints as deferrable:
-- Recreate the constraint to be deferrable
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers(id)
DEFERRABLE INITIALLY DEFERRED;Now constraint checks happen at commit time, not immediately:
BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (999, 100);
INSERT INTO customers (id, name) VALUES (999, 'New Customer');
COMMIT; -- Constraints checked here, both records existNote: DEFERRABLE INITIALLY IMMEDIATE (the default) checks constraints immediately. DEFERRABLE INITIALLY DEFERRED defers until commit.
For INSERT/UPDATE operations that might fail:
-- Use ON CONFLICT for UNIQUE constraint violations
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;In PL/pgSQL functions, catch violations:
CREATE FUNCTION safe_insert_order(p_customer_id INT, p_amount DECIMAL) RETURNS void AS $$
BEGIN
INSERT INTO orders (customer_id, amount) VALUES (p_customer_id, p_amount);
EXCEPTION WHEN foreign_key_violation THEN
RAISE EXCEPTION 'Customer % does not exist', p_customer_id;
END;
$$ LANGUAGE plpgsql;Application drivers can also use savepoints to catch and recover from constraint violations:
# Python example with savepoints
try:
cursor.execute("SAVEPOINT sp1")
cursor.execute("INSERT INTO users (email) VALUES (%s)", (email,))
except IntegrityError:
cursor.execute("ROLLBACK TO SAVEPOINT sp1")
# Handle duplicate, try different value, etc.If the error mentions a CHECK constraint:
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE table_name = 'products';Common CHECK violations:
-- This might have a CHECK (price > 0) constraint
INSERT INTO products (name, price) VALUES ('Widget', -10); -- FAILS
-- Use valid values
INSERT INTO products (name, price) VALUES ('Widget', 10); -- OKAdjust your values to satisfy the constraint rules.
PostgreSQL's strict transaction behavior differs from some databases. It provides ACID guarantees by aborting the entire transaction on any constraint violation. This has performance benefits: rollback is instant because no changes are written. Use savepoints in application drivers for fine-grained error recovery—PgJDBC supports ON_ERROR_ROLLBACK=true to create implicit savepoints. For interactive SQL, psql's \set ON_ERROR_ROLLBACK on prevents a typo from losing all work. The "current transaction is aborted" error appears after the actual constraint violation; you must ROLLBACK or COMMIT to clear this state. Deferred constraints add a small performance cost at commit time but enable cleaner application logic when transactions must temporarily violate constraints.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL