TRUNCATE SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

Remove all rows from a table instantly, without logging individual row deletions.

Efficiently removes all rows from a table by deallocating pages rather than deleting row-by-row. Much faster than DELETE for full table clears. Cannot be rolled back in some databases without explicit transaction handling; check your engine.

Compatibility

SQL TRUNCATE Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	all	Resets AUTO_INCREMENT counter to its starting value. Cannot be rolled back in InnoDB without an explicit transaction.
PostgreSQL	Native	all	Uniquely transactional — can be rolled back within a transaction. Supports RESTART IDENTITY / CONTINUE IDENTITY (sequence handling) and CASCADE / RESTRICT (foreign key handling).
SQL Server	Native	all	Resets identity counter. Cannot truncate a table referenced by an active foreign key constraint.
Oracle	Native	all	DDL operation — implicitly commits the current transaction. Cannot be rolled back.
SQLite	Not Supported	-	No TRUNCATE command. Use DELETE FROM tbl (no WHERE clause). Reclaim space with VACUUM.

Details

Much faster than DELETE on large tables because it deallocates data pages rather than logging row-by-row. PostgreSQL is unique in making TRUNCATE fully transactional. SQLite lacks the command entirely.

Standard Syntax

TRUNCATE TABLE table_name;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

Resets AUTO_INCREMENT counter to its starting value. Cannot be rolled back in InnoDB without an explicit transaction.

TRUNCATE TABLE tbl;

PostgreSQL Native syntax

Uniquely transactional — can be rolled back within a transaction. Supports RESTART IDENTITY / CONTINUE IDENTITY (sequence handling) and CASCADE / RESTRICT (foreign key handling).

TRUNCATE TABLE tbl; TRUNCATE TABLE tbl RESTART IDENTITY CASCADE;

SQL Server Native syntax

Resets identity counter. Cannot truncate a table referenced by an active foreign key constraint.

TRUNCATE TABLE tbl;

Oracle Native syntax

DDL operation — implicitly commits the current transaction. Cannot be rolled back.

TRUNCATE TABLE tbl;

SQLite Alternative syntax

No TRUNCATE command. Use DELETE FROM tbl (no WHERE clause). Reclaim space with VACUUM.

DELETE FROM tbl; -- equivalent; pair with VACUUM to reclaim space