DML

TRUNCATE

Q: Is TRUNCATE reversible?

Postgres / SQL Server / DuckDB inside a transaction: yes, ROLLBACK reverses it. MySQL InnoDB and Oracle: no. DDL implicitly commits, so TRUNCATE is permanent the moment it runs. Treat as irreversible on those engines.

Q: Does TRUNCATE reset auto-increment counters?

Usually yes. Postgres: pass RESTART IDENTITY (it doesn't reset by default). MySQL: yes, by default. SQL Server: yes. If downstream systems rely on monotonically-increasing IDs, this can produce ID collisions with previously-emitted data, so be aware.

Q: How do I empty all tables in a schema?

Generate a script: SELECT 'TRUNCATE TABLE ' table_name ';' FROM information_schema.tables WHERE table_schema = 'staging'. Then execute the output. Be very deliberate: staging schema only, never production data. Postgres has TRUNCATE table1, table2, table3 as a multi-table form.

Empty a table fast. Conceptually DELETE FROM t with no WHERE, but implemented very differently: most engines deallocate the underlying pages instead of removing rows one at a time. Orders of magnitude quicker on big tables; pays for it in transactional and trigger semantics that don't match DELETE.

Syntax #

sql

TRUNCATE TABLE table_name;

-- Postgres extras.
TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

Example #

Loading SQL editor...

TRUNCATE vs DELETE #

	`DELETE FROM t`	`TRUNCATE TABLE t`
Speed on large tables	slow (row-by-row)	fast (page-level)
Transactional	yes (everywhere)	yes in Postgres / SQL Server; no (auto-commit) in MySQL InnoDB, Oracle
Per-row triggers	fire	don't fire
Foreign key behavior	obeys `ON DELETE` actions	refused if other tables have FKs pointing in (unless `CASCADE` is given and supported)
Identity / sequence reset	not reset	typically reset (Postgres requires `RESTART IDENTITY`)
WAL / replication cost	one log entry per row	one log entry total
Partial deletes (`WHERE`)	yes	no

Dialect notes #

Postgres: TRUNCATE is fully transactional. RESTART IDENTITY resets sequences; CASCADE truncates dependent tables in one go.
MySQL (InnoDB): implicit commit, so you cannot ROLLBACK a TRUNCATE. Treat it as permanent. Refused on tables referenced by other tables' FKs unless those are dropped first.
SQL Server: transactional. Permission-distinct from DELETE (ALTER permission required).
Oracle: implicit commit, like MySQL. REUSE STORAGE / DROP STORAGE controls what happens to the deallocated extents.
SQLite: no TRUNCATE keyword. DELETE FROM t (with no WHERE) triggers an internal "truncate optimization" that's similarly fast.
BigQuery: TRUNCATE TABLE exists; behaves transactionally and resets the table to a fresh state.
Snowflake: TRUNCATE TABLE keeps the schema and metadata; transactional and reversible via Time Travel.

FAQ #

When should I use TRUNCATE vs DELETE?

TRUNCATE for "empty this whole table fast": staging tables, scratch tables, ETL intermediates. DELETE for "remove specific rows by WHERE", meaning anything user-facing or production-critical. TRUNCATE is orders of magnitude faster on big tables; DELETE gives you control and full transactional safety.

Is TRUNCATE reversible?

Postgres / SQL Server / DuckDB inside a transaction: yes, ROLLBACK reverses it. MySQL InnoDB and Oracle: no. DDL implicitly commits, so TRUNCATE is permanent the moment it runs. Treat as irreversible on those engines.

Does TRUNCATE reset auto-increment counters?

Usually yes. Postgres: pass RESTART IDENTITY (it doesn't reset by default). MySQL: yes, by default. SQL Server: yes. If downstream systems rely on monotonically-increasing IDs, this can produce ID collisions with previously-emitted data, so be aware.

Can TRUNCATE be used on a table referenced by foreign keys?

On most engines, no: TRUNCATE is refused if other tables have FKs pointing to this one. Postgres allows TRUNCATE ... CASCADE to truncate dependent tables in one statement. MySQL refuses unless FKs are dropped first.

Do triggers fire on TRUNCATE?

Per-row triggers do NOT fire on TRUNCATE on most engines (the speed comes from skipping per-row work). Statement-level triggers may fire. If your application relies on triggers for audit logging or downstream sync, use DELETE instead, or update your audit pattern.

How do I empty all tables in a schema?

Generate a script: SELECT 'TRUNCATE TABLE ' || table_name || ';' FROM information_schema.tables WHERE table_schema = 'staging'. Then execute the output. Be very deliberate: staging schema only, never production data. Postgres has TRUNCATE table1, table2, table3 as a multi-table form.

Warning

**On MySQL and Oracle, TRUNCATE is not a "safe alternative to DELETE".** Once it commits (which is immediate), you cannot ROLLBACK. Used carelessly against production it has the same blast radius as DROP TABLE for the row data. Reserve it for staging tables, scratch tables, and contexts where you've explicitly decided "every row in here is disposable." For anything you'd hate to lose, use DELETE inside a transaction.