SQL guide · Tables, views & databases (DDL)

SQL TRUNCATE TABLE: Remove All Rows from a Table Quickly

The TRUNCATE TABLE statement deletes all rows from a table in one fast operation and keeps the table definition, its columns, indexes and constraints in place. After it runs you have an empty table that is ready to be filled again. It is typically used to reset staging tables before a data load, to clear out test data, or to empty a log table that has grown too large.

It is usually much faster than DELETE FROM table_name without a WHERE clause. Instead of removing rows one at a time and logging each deletion, most databases deallocate the data pages in bulk and log only that action. The trade-off is that you cannot filter rows, since truncate always removes everything, and it often resets auto-increment or identity counters back to their starting value.

Truncate behaves differently from delete in other ways too. It generally does not fire row-level delete triggers, it can require stronger privileges, and it may be blocked when other tables hold foreign keys that reference the table. Whether it can be rolled back depends on the database. Compare the options carefully before choosing: use DELETE to remove some rows, TRUNCATE TABLE to remove all rows but keep the table, and DROP TABLE to remove the table itself.

Basic syntax

TRUNCATE TABLE table_name;

-- PostgreSQL extras
TRUNCATE TABLE table_name [, table_name2 ...]
  [RESTART IDENTITY | CONTINUE IDENTITY]
  [CASCADE | RESTRICT];

The table name is the only required part. In PostgreSQL the keyword TABLE is optional, you can list several tables, RESTART IDENTITY resets sequences, and CASCADE also truncates tables with foreign keys pointing at it.

SQL TRUNCATE TABLE examples

Empty a staging table

This removes every row from staging_orders while keeping its columns and indexes. It is a common first step in a load job: clear the table, then insert the fresh batch.

TRUNCATE TABLE staging_orders;

INSERT INTO staging_orders (order_id, customer_id, total)
SELECT order_id, customer_id, total
FROM raw_orders;

Truncate and reset the ID counter in PostgreSQL

With RESTART IDENTITY the sequence behind an identity or serial column goes back to 1, so new rows start numbering from the beginning. Without it, the counter would continue from where it left off.

TRUNCATE TABLE event_log RESTART IDENTITY;

Truncate tables that have foreign keys

A table referenced by a foreign key cannot be truncated on its own in most systems. In PostgreSQL you can truncate the parent and child together, or add CASCADE to include every referencing table. Be sure you want those tables emptied too.

TRUNCATE TABLE order_items, orders CASCADE;

Compare with DELETE

A DELETE without a filter also empties the table but works row by row, logs each row and fires triggers. Use it when you need those behaviors or when you want a filtered removal instead of clearing everything.

-- Remove only old rows
DELETE FROM event_log
WHERE created_at < '2026-01-01';

-- Remove every row, row by row
DELETE FROM event_log;

Where SQL dialects differ

PostgreSQL: Transactional, so a truncate can be rolled back inside a transaction. Supports multiple tables, RESTART IDENTITY and CASCADE. It takes an ACCESS EXCLUSIVE lock on the table.

MySQL: Behaves like a drop and recreate of the table, causes an implicit commit and cannot be rolled back. It resets AUTO_INCREMENT to its initial value. It fails on InnoDB tables referenced by foreign keys unless checks are disabled.

SQL Server: Can be rolled back inside a transaction. It resets IDENTITY to the seed. It is not allowed on a table referenced by a foreign key, even if the child table is empty. It requires ALTER TABLE permission.

SQLite: There is no TRUNCATE TABLE statement. Use DELETE FROM table_name, which SQLite optimizes into a fast truncate when there is no WHERE clause, and reset autoincrement counters by clearing the sqlite_sequence entry.

Oracle: Is DDL and commits implicitly, so it cannot be rolled back. Supports TRUNCATE TABLE t DROP STORAGE or REUSE STORAGE, and CASCADE from 12c to include child tables with ON DELETE CASCADE.

Common mistakes

  • Expecting to filter rows with WHERE. Truncate has no filter and always removes everything, so use DELETE for partial removal.
  • Assuming it can be rolled back. In MySQL and Oracle it commits immediately, so a mistake is permanent without a backup.
  • Running it on a table that other tables reference and being surprised by a foreign key error. Truncate dependents first, or use CASCADE where available.
  • Relying on delete triggers to fire for auditing. Truncate normally bypasses row-level triggers.
  • Confusing it with DROP TABLE. Truncate keeps the table, while drop removes the definition too.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator