How to TRUNCATE TABLE in PostgreSQL

Why choose TRUNCATE TABLE over DELETE?

TRUNCATE TABLE is bulk, metadata-level deletion. PostgreSQL deallocates data pages instead of logging every row, making it dramatically faster and less WAL-intensive than DELETE for large datasets.

What is the basic TRUNCATE TABLE syntax?

Use TRUNCATE TABLE table_name; to wipe data instantly. Add options such as RESTART IDENTITY, CASCADE, or multiple table names for more control.

How do I reset SERIAL/IDENTITY columns?

Add RESTART IDENTITY to zero out sequence generators so new rows start at 1. Omitting it keeps sequence values untouched.

When do I need CASCADE?

Use CASCADE when child tables reference the target through foreign keys. PostgreSQL truncates dependent tables in the same transaction, preventing constraint errors.

Can I truncate multiple tables at once?

Yes. List table names comma-separated. PostgreSQL locks and truncates them atomically, ideal for clearing staging schemas after ETL jobs.

Example: Clear all orders for a fresh import

BEGIN;
TRUNCATE TABLE Orders, OrderItems RESTART IDENTITY CASCADE;
COMMIT;

This script erases historical order data, resets order IDs, and removes dependent order items—all in milliseconds.

Best practices for safe use

Wrap TRUNCATE in a transaction so you can ROLLBACK if needed. Always run on non-production first, verify row counts, and back up critical tables.

Should I grant TRUNCATE to everyone?

No. Restrict the privilege to roles that truly need it. TRUNCATE is irreversible once committed.

DELETE vs. TRUNCATE: quick comparison

DELETE supports WHERE clauses, fires row-level triggers, and returns affected row count. TRUNCATE is all-or-nothing, bypasses row triggers, and is non-recoverable without backups.