Postgres TRUNCATE TABLE with CASCADE and RESTART IDENTITY

TRUNCATE removes every row from a table in one step. It does not scan rows the way DELETE does; it gives the table new, empty data files, so a table of millions of rows empties in milliseconds. The TABLE keyword is optional.

TRUNCATE TABLE audit_log_v2;

The examples use the seed-demo shop schema: users, orders (references users) and order_items (references orders). Every statement and message below came from runs on PostgreSQL 16.13 and 18.6, and both versions behaved the same.

TRUNCATE syntax

The full statement takes a list of tables and two optional switches. Defaults are CONTINUE IDENTITY and RESTRICT.

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ...]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];

There is no WHERE clause and no IF EXISTS. Both are syntax errors, and a missing table fails with "relation "nope" does not exist":

TRUNCATE audit_log WHERE id > 5;
ERROR:  syntax error at or near "WHERE"

TRUNCATE TABLE IF EXISTS audit_log;
ERROR:  syntax error at or near "EXISTS"

To remove only some rows, use DELETE ... WHERE.

TRUNCATE CASCADE and the foreign key error

A table that another table references cannot be truncated on its own, even when the referencing table is already empty. This is the error, word for word:

TRUNCATE TABLE orders;
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "order_items" references "orders".
HINT:  Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

CASCADE follows the foreign keys and empties every table that references the target, and the tables that reference those, in the same statement. PostgreSQL prints a notice for each one.

TRUNCATE orders CASCADE;
-- NOTICE:  truncate cascades to table "order_items"

CASCADE follows the whole chain, so TRUNCATE users CASCADE would also empty orders, order_items, subscriptions and audit_log_v2. Read the notices, or check \d users for its "Referenced by" list first. RESTRICT is the default and only spells out the refusal above.

RESTART IDENTITY vs CONTINUE IDENTITY

By default the sequences behind serial and GENERATED ... AS IDENTITY columns keep their value, so after emptying a table of 300 orders the next order still gets id 301. Add RESTART IDENTITY to start them over at 1.

TRUNCATE orders, order_items RESTART IDENTITY;
INSERT INTO orders (user_id, total_amount) VALUES (1, 5) RETURNING id;
-- id = 1

With CASCADE, the reset reaches the cascaded tables too: TRUNCATE orders RESTART IDENTITY CASCADE set both orders and order_items back to 1. A sequence the table does not own, used through DEFAULT nextval('ticket_seq'), is not reset. Reset it yourself:

SELECT setval('ticket_seq', 1, false);

Truncate multiple tables at once

List the tables separated by commas. They are emptied together in one statement, which is also how you satisfy the foreign key check without CASCADE: every referencing table is in the list.

TRUNCATE orders, order_items;

Example: clear the shop's order data and start the ids over, leaving users untouched.

TRUNCATE order_items, orders RESTART IDENTITY;

TRUNCATE vs DELETE vs DROP

All three get rid of data, but they work differently. Timings are from a 1,000,000 row table on the same machine.

Triggers are the difference that bites. A FOR EACH ROW delete trigger, such as one that writes to an audit table, does not run on TRUNCATE. Add a statement trigger, with a trigger function of your own, if you need to react to it:

CREATE TRIGGER orders_truncated
AFTER TRUNCATE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION log_truncate();

TRUNCATE is also not MVCC-safe. A REPEATABLE READ transaction that took its snapshot before another session's truncate committed sees the table as empty afterwards, not the rows that were there when it started. DELETE does not have this problem.

DROP TABLE on a referenced table fails the same way, with "cannot drop table users because other objects depend on it". DROP TABLE users CASCADE then drops only the foreign key constraint on orders ("drop cascades to constraint orders_user_id_fkey on table orders"), and the orders keep their rows.

TRUNCATE in a transaction and rollback

TRUNCATE is transactional in PostgreSQL, unlike in MySQL. Run it inside BEGIN and a ROLLBACK brings every row back.

BEGIN;
TRUNCATE audit_log_v2;
SELECT count(*) FROM audit_log_v2;  -- 0
ROLLBACK;
SELECT count(*) FROM audit_log_v2;  -- original count again

RESTART IDENTITY is rolled back as well: the sequence goes back to its old value. Once the transaction commits, there is no undo.

Locks, and why TRUNCATE hangs

TRUNCATE takes an ACCESS EXCLUSIVE lock on every table it empties, the strongest lock there is. It has to wait for every open transaction that has touched the table, even a plain SELECT in an idle transaction, and while it waits, new queries on the table queue up behind it. That is the usual reason a TRUNCATE seems to take forever on a tiny table.

Set a lock_timeout so it gives up instead of blocking the table:

SET lock_timeout = '2s';
TRUNCATE audit_log_v2;
-- ERROR:  canceling statement due to lock timeout

Example: from a second session, find what the waiting truncate is blocked by.

SELECT pid, wait_event_type, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE query ILIKE 'truncate%';

The blocked_by column lists the process ids holding the table. Finish or cancel those sessions, for example with SELECT pg_cancel_backend(pid). Inside a transaction the lock is held until COMMIT, so keep a truncate near the end of a long transaction, not the start.

TRUNCATE permission

Truncating needs the TRUNCATE privilege on the table, or ownership. DELETE rights are not enough:

ERROR:  permission denied for table audit_log

Grant it per table, or on every existing table in a schema:

GRANT TRUNCATE ON orders, order_items TO app_user;
GRANT TRUNCATE ON ALL TABLES IN SCHEMA public TO app_user;

SELECT has_table_privilege('app_user', 'orders', 'TRUNCATE');

ON ALL TABLES covers tables that exist now. For tables created later, add ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT TRUNCATE ON TABLES TO app_user.

Truncate all tables in a schema or database

There is no TRUNCATE SCHEMA command. Build one TRUNCATE with every table in the list, so the foreign keys are satisfied without CASCADE, and run it in a DO block:

DO $$
DECLARE
  tables text;
BEGIN
  SELECT string_agg(format('%I.%I', schemaname, tablename), ', ')
    INTO tables
    FROM pg_tables
   WHERE schemaname = 'public';
  EXECUTE 'TRUNCATE ' || tables || ' RESTART IDENTITY';
END $$;

For every table in the database, swap the filter for schemaname NOT IN ('pg_catalog', 'information_schema'). To keep a table, such as a migrations table, add AND tablename <> 'schema_migrations'. If the filter matches no tables, the block fails with "query string argument of EXECUTE is null", which is a safe way to fail.

Example: in psql, \gexec runs each generated statement in turn, which is handy when you want to see them.

SELECT format('TRUNCATE %I.%I RESTART IDENTITY CASCADE', schemaname, tablename)
FROM pg_tables
WHERE schemaname = 'public' \gexec

Partitioned tables

Truncating a partitioned table empties all of its partitions. Truncate a single partition by name to clear one range, which is a fast way to drop old data.

TRUNCATE events_2025;
TRUNCATE events;

ONLY is refused on the parent: "cannot truncate only a partitioned table", with the hint to drop ONLY or truncate the partitions directly.