TRUNCATE TABLE

What is TRUNCATE TABLE?

TRUNCATE TABLE removes all rows from a table in one fast operation. Unlike DELETE, it does not scan every row individually, making it significantly faster for large tables.

The table structure, columns, indexes, and constraints remain intact. Only the data is removed.

Syntax

TRUNCATE TABLE table_name;

To truncate multiple tables at once:

TRUNCATE TABLE table1, table2;

To also reset identity/serial columns:

TRUNCATE TABLE table_name RESTART IDENTITY;

When to Use

• Clearing staging or temporary tables before loading new data
• Resetting a table in a test environment
• Removing all data faster than a DELETE without a WHERE clause

Key Points

1. No WHERE Clause — TRUNCATE always removes all rows. Use DELETE if you need to remove a subset.
2. Faster Than DELETE — TRUNCATE does not generate individual row-deletion logs, so it is much faster on large tables.
3. RESTART IDENTITY — Resets auto-increment (SERIAL/BIGSERIAL) counters back to their initial value.
4. CASCADE — TRUNCATE TABLE ... CASCADE also truncates tables with foreign-key references to the target table.
5. Not Trigger-Friendly — TRUNCATE fires BEFORE TRUNCATE and AFTER TRUNCATE triggers, not per-row DELETE triggers.