SQL Reference ยท Statements and Clauses
SQL TRUNCATE TABLE statement
Reference for SQL TRUNCATE TABLE: empty a table quickly, how it differs from DELETE, identity resets, foreign-key limits and the SQLite equivalent.
TRUNCATE TABLE removes all rows from a table in one fast operation. Instead of deleting rows one at a time, the database discards the table's data wholesale, keeping the table definition, columns and indexes. It is meant for clearing staging tables, test data and caches.
SQLite has no TRUNCATE statement. There, DELETE FROM table_name; with no WHERE clause does the same job, and SQLite optimizes it into a quick "truncate" internally.
-- PostgreSQL, MySQL, SQL Server, Oracle TRUNCATE TABLE staging_orders; -- SQLite DELETE FROM staging_orders;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
TRUNCATE TABLE table_name; TRUNCATE TABLE table_name RESTART IDENTITY CASCADE; -- PostgreSQL options
| Part | Description |
|---|---|
table_name |
The table to empty. TABLE is optional in PostgreSQL and MySQL. |
RESTART IDENTITY |
PostgreSQL only: reset the table's sequences. CONTINUE IDENTITY is the default. |
CASCADE |
PostgreSQL only: also truncate tables that have foreign keys pointing here. |
Result: no row count is reported. There is no WHERE; you cannot truncate part of a table.
Dialect differences
| Dialect | Behavior |
|---|---|
| SQLite | No TRUNCATE. Use DELETE FROM t;. Reset an AUTOINCREMENT counter by deleting its row in sqlite_sequence. |
| PostgreSQL | Transactional (can be rolled back). Fires TRUNCATE triggers, not row triggers. Needs CASCADE when other tables reference it. Identity values restart only with RESTART IDENTITY. |
| MySQL | Implicit commit (cannot be rolled back). Resets AUTO_INCREMENT. Refuses tables referenced by foreign keys. |
| SQL Server | Can be rolled back inside a transaction. Resets identity to its seed. Blocked by foreign-key references; needs ALTER permission on the table. |
| Oracle | DDL: commits implicitly and cannot be rolled back. Supports TRUNCATE TABLE t CASCADE (12c+) for referencing tables with ON DELETE CASCADE. |
Usage notes
- Faster than
DELETEon big tables because it skips per-row work and logging; for small tables the difference is negligible. - No
WHERE. For a filtered removal useDELETE. - Usually not undoable in MySQL and Oracle, so the safety net of a transaction is missing there. PostgreSQL and SQL Server allow rollback.
- Foreign keys commonly block truncation; clear child tables first or use the dialect's
CASCADEoption. - Triggers: ordinary
ON DELETErow triggers do not fire forTRUNCATE. - Permissions: it is often a separate privilege from
DELETE. - SQLite file size does not shrink after the
DELETE; runVACUUM. DROP TABLEthenCREATE TABLEalso empties a table but loses indexes, grants and dependent objects.
Related
DELETE,DROP TABLE,VACUUM, sequences and auto-increment- Lessons: DELETE, Alter and drop