How to TRUNCATE TABLE in PostgreSQL

What does TRUNCATE TABLE do in PostgreSQL?

TRUNCATE TABLE instantly deletes every row in the specified table(s). PostgreSQL drops and recreates the data pages, so the action is nearly constant-time, uses little WAL, and releases disk space immediately after commit.

How is TRUNCATE TABLE different from DELETE?

DELETE scans the table, logs each removed row, and fires DELETE triggers. TRUNCATE logs only the fact that the entire relation was removed, bypasses row-level triggers, and acquires an ACCESS EXCLUSIVE lock.

What is the basic syntax?

Use the keyword TABLE optionally, list one or more tables, and pick identity and FK behaviours:

TRUNCATE TABLE table1 [, table2]
[RESTART IDENTITY | CONTINUE IDENTITY]
[CASCADE | RESTRICT];

Can I truncate multiple tables at once?

Yes—comma-separate them. PostgreSQL handles each with the same lock level, ensuring consistency and speed when purging several staging tables together.

Should I use CASCADE when foreign keys exist?

Use CASCADE only when child tables should be truncated automatically. Without it, PostgreSQL errors if foreign keys reference the target. RESTRICT (default) protects accidental data loss.

When would I RESTART IDENTITY?

RESTART IDENTITY resets any sequences owned by the truncated tables to their start values, handy for demo or test databases. CONTINUE IDENTITY leaves sequences untouched to avoid PK conflicts with replicas.

Example: Clearing stale demo orders

TRUNCATE TABLE Orders, OrderItems
RESTART IDENTITY CASCADE;

The command wipes all demo sales data, truncates the child OrderItems automatically, and restarts both tables’ sequences at 1.

Best practices for using TRUNCATE TABLE

Run it in maintenance windows or short transactions to minimise blocking. Always confirm you are in the correct database. Keep backups or snapshots if accidental loss is unacceptable.

Common mistakes and how to avoid them

Forgetting CASCADE causes FK violations; holding the lock too long blocks writers. See detailed fixes below.

Should I wrap TRUNCATE in a transaction?

Yes, you can. The data is removed instantly, but changes become visible only after COMMIT. ROLLBACK restores the table.

Does TRUNCATE fire triggers?

Row-level triggers do not fire; statement-level triggers (AFTER TRUNCATE) do. Add auditing logic there if needed.

How can I grant permission to truncate?

GRANT a role the TRUNCATE privilege or give them table ownership. Without it, superusers only can truncate.