This error occurs when you try to TRUNCATE a table that is referenced by another table through a foreign key constraint. TRUNCATE is much faster than DELETE but cannot proceed if child tables have references. Use CASCADE to truncate all related tables together, truncate tables in dependency order, or temporarily disable constraints.
PostgreSQL's TRUNCATE statement is an optimized way to delete all rows from a table without scanning it individually. However, TRUNCATE prevents data integrity violations by refusing to truncate tables that are referenced by foreign keys from other tables. When PostgreSQL detects that you're trying to truncate a table with incoming foreign key references, it raises this error. This is a safety feature—truncating a parent table while child records still reference it would break referential integrity.
The error will tell you which table cannot be truncated and which constraint is blocking it:
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "orders" references "customers" via foreign key "orders_customer_id_fkey".This tells you:
- You're trying to TRUNCATE table customers
- Table orders has child records with foreign keys pointing to customers
- The constraint name is orders_customer_id_fkey
The simplest solution is to add CASCADE, which will truncate all dependent tables:
-- Truncate the parent table and all child tables at once
TRUNCATE TABLE customers CASCADE;This is equivalent to:
TRUNCATE TABLE customers, orders CASCADE;Note: CASCADE truncates ALL tables that reference the one you specified, not just direct children. Be careful you understand the full impact.
If you only want to truncate certain tables, truncate child tables BEFORE parent tables:
-- Correct order: child (orders) BEFORE parent (customers)
TRUNCATE TABLE orders;
TRUNCATE TABLE customers;This works because once the child table is empty, there are no foreign key references left.
For multiple levels of relationships, truncate in reverse dependency order:
TRUNCATE TABLE order_items; -- Deepest child
TRUNCATE TABLE orders; -- Parent of order_items
TRUNCATE TABLE customers; -- Parent of ordersWhen truncating tables with auto-incrementing ID columns, optionally reset the sequence:
-- Truncate and reset ID counter to 1
TRUNCATE TABLE customers RESTART IDENTITY CASCADE;Without RESTART IDENTITY:
-- Just truncates, but next INSERT will continue from previous counter
TRUNCATE TABLE customers CASCADE;
-- If you deleted up to ID 1000, the next insert gets ID 1001Use RESTART IDENTITY only if you want to reuse lower ID values.
For large cleanup operations, you can disable constraints, truncate, then re-enable:
-- Disable all triggers (including FK constraints)
ALTER TABLE customers DISABLE TRIGGER ALL;
ALTER TABLE orders DISABLE TRIGGER ALL;
-- Now truncate without restriction
TRUNCATE TABLE customers, orders;
-- Re-enable everything
ALTER TABLE customers ENABLE TRIGGER ALL;
ALTER TABLE orders ENABLE TRIGGER ALL;Caution: Use only in controlled scenarios where you're sure referential integrity won't be violated.
Always wrap TRUNCATE operations in transactions so you can ROLLBACK if something goes wrong:
BEGIN;
TRUNCATE TABLE customers CASCADE;
-- Verify the data you want is actually gone
SELECT COUNT(*) FROM customers; -- Should be 0
-- Add more checks as needed
COMMIT; -- Or ROLLBACK if something looks wrongIf you realize you made a mistake before COMMIT, ROLLBACK will undo the truncate.
TRUNCATE vs DELETE Performance: TRUNCATE is much faster because it doesn't scan individual rows—it deallocates the table pages directly. For large tables, the difference is orders of magnitude. However, TRUNCATE doesn't fire ON DELETE triggers (only ON TRUNCATE triggers).
Sequence Behavior Without RESTART IDENTITY: If your table has a SERIAL or BIGSERIAL column (which uses a sequence), the sequence counter is NOT reset by TRUNCATE unless you use RESTART IDENTITY. This means after truncating, the next INSERT will get an ID one higher than the last deleted row's ID, leaving gaps.
CASCADE and Multiple Levels: When you use CASCADE, PostgreSQL truncates not just direct children, but all descendant tables. If you have A ← B ← C (C references B, B references A), then TRUNCATE A CASCADE will truncate all three. Be very careful with this in complex schemas.
Constraint Checking Inside Transactions: TRUNCATE constraints are always checked immediately, even if you SET CONSTRAINTS to DEFERRED. This is different from INSERT/UPDATE/DELETE constraints. If a table is referenced by a foreign key, CASCADE is required—there's no deferred alternative.
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