Skip to main content

TRUNCATE

Empties a table or set of tables of all rows.

Syntax​

TRUNCATE [TABLE] [ONLY] <name> [ * ] [, ...] 
    [CASCADE | RESTRICT]

Description​

TRUNCATE quickly removes all rows from a table or set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

You must have the TRUNCATE privilege on the table to truncate it.

Parameters​

  • <name>

    The name of the table to truncate, which can be specified with the schema qualification.

  • ONLY

    An optional keyword that has no effect.

  • CASCADE or RESTRICT

    An optional keyword that has no effect.

Usage notes​

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table that it operates on, which blocks all other concurrent operations on the table. If you require concurrent access to a table, use the DELETE command instead.

TRUNCATE will not truncate any tables that inherit from the named table. Only the named table is truncated, not its child tables.

TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred.

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

Examples​

Empty the tables named sales and topsellers:

TRUNCATE sales, topsellers;

SQL standard compatibility​

The SQL:2008 standard includes a TRUNCATE command with the syntax TRUNCATE TABLE <tablename>. Some of the concurrency behavior of this command is left implementation-defined by the standard, so the above notes should be considered and compared with other implementations if necessary.