TRUNCATE

TRUNCATE removes all rows from a table in a single fast operation, without logging individual row deletions the way DELETE does.

Overview

TRUNCATE is a DDL-adjacent statement for quickly emptying a table:

Copy code
TRUNCATE TABLE staging_orders;

In most databases, TRUNCATE is faster than an unconditional DELETE FROM because it deallocates data pages in bulk rather than logging and removing rows one at a time. It cannot take a WHERE clause — it always removes every row.

TRUNCATE vs DELETE vs DROP TABLE

TRUNCATE: removes all rows, keeps the table structure (columns, constraints), typically minimal per-row logging.
DELETE FROM table (no WHERE): removes all rows too, but is generally slower in traditional row-store databases because it logs each row deletion; can be filtered with WHERE.
DROP TABLE: removes the table definition entirely, not just its rows.

In many relational databases, TRUNCATE also resets any auto-increment/identity counter back to its starting value, and in some systems it cannot be rolled back inside a transaction as easily as DELETE — behavior that varies by engine.

DuckDB specifics

DuckDB supports both TRUNCATE TABLE tbl and the shorthand TRUNCATE tbl. Under the hood, DuckDB's TRUNCATE behaves as a straightforward equivalent to running DELETE FROM tbl with no WHERE clause — it is fully transactional (can be rolled back as part of a larger transaction) rather than an unloggable, non-transactional bulk operation as in some other systems. If a sequence backs an ID column's default, TRUNCATE does not reset that sequence in DuckDB — the sequence continues from wherever it left off unless you explicitly reset it.

Related terms

DELETE statement →

The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.

table →

A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.

Referential integrity →

Referential integrity is the guarantee that a reference from one table to another — typically a foreign key — always points to a row that actually exists, preventing orphaned records.

UPDATE statement →

The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.

ALTER TABLE statement →

The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.

Transactions →

A database transaction is a sequence of one or more operations that are executed as a single logical unit of work, either fully applied or fully rolled back.

FAQS

In many traditional databases, yes, because TRUNCATE deallocates pages in bulk rather than logging individual row deletions. In DuckDB specifically, TRUNCATE is implemented as an equivalent to an unconditional DELETE FROM, so the performance difference is less pronounced.

No. If a column's default value comes from a sequence via nextval(), TRUNCATE does not reset that sequence — it continues generating values from where it left off.