TRUNCATE

TRUNCATE

7

SQL - Language Statements

TRUNCATE

empty a table or set of tables

TRUNCATE \[ TABLE \] \[ ONLY \]

name

\[ \* \] \[, \... \] \[ RESTART IDENTITY \| CONTINUE IDENTITY \] \[
CASCADE \| RESTRICT \]

Description
===========

`TRUNCATE` quickly removes all rows from a 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.

Parameters
==========

name

:   The name (optionally schema-qualified) of a table to truncate. If
    `ONLY` is specified before the table name, only that table is
    truncated. If `ONLY` is not specified, the table and all its
    descendant tables (if any) are truncated. Optionally, `*` can be
    specified after the table name to explicitly indicate that
    descendant tables are included.

`RESTART IDENTITY`

:   Automatically restart sequences owned by columns of the truncated
    table(s).

`CONTINUE IDENTITY`

:   Do not change the values of sequences. This is the default.

`CASCADE`

:   Automatically truncate all tables that have foreign-key references
    to any of the named tables, or to any tables added to the group due
    to `CASCADE`.

`RESTRICT`

:   Refuse to truncate if any of the tables have foreign-key references
    from tables that are not listed in the command. This is the default.

Notes
=====

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

`TRUNCATE` acquires an `ACCESS EXCLUSIVE` lock on each table it operates
on, which blocks all other concurrent operations on the table. When
`RESTART IDENTITY` is specified, any sequences that are to be restarted
are likewise locked exclusively. If concurrent access to a table is
required, then the `DELETE` command should be used instead.

`TRUNCATE` cannot be used on a table that has foreign-key references
from other tables, unless all such tables are also truncated in the same
command. Checking validity in such cases would require table scans, and
the whole point is not to do one. The `CASCADE` option can be used to
automatically include all dependent tables MDASH but be very careful
when using this option, or else you might lose data you did not intend
to! Note in particular that when the table to be truncated is a
partition, siblings partitions are left untouched, but cascading occurs
to all referencing tables and all their partitions with no distinction.

`TRUNCATE` will not fire any `ON DELETE` triggers that might exist for
the tables. But it will fire `ON TRUNCATE` triggers. If `ON TRUNCATE`
triggers are defined for any of the tables, then all `BEFORE TRUNCATE`
triggers are fired before any truncation happens, and all `AFTER
   TRUNCATE` triggers are fired after the last truncation is performed
and any sequences are reset. The triggers will fire in the order that
the tables are to be processed (first those listed in the command, and
then any that were added due to cascading).

`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. See [???](#mvcc-caveats) for more
details.

`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.

When `RESTART IDENTITY` is specified, the implied
`ALTER SEQUENCE RESTART` operations are also done transactionally; that
is, they will be rolled back if the surrounding transaction does not
commit. Be aware that if any additional sequence operations are done on
the restarted sequences before the transaction rolls back, the effects
of these operations on the sequences will be rolled back, but not their
effects on `currval()`; that is, after the transaction `currval()` will
continue to reflect the last sequence value obtained inside the failed
transaction, even though the sequence itself may no longer be consistent
with that. This is similar to the usual behavior of `currval()` after a
failed transaction.

`TRUNCATE` can be used for foreign tables if supported by the foreign
data wrapper, for instance, see [???](#postgres-fdw).

Examples
========

Truncate the tables `bigtable` and `fattable`:

    TRUNCATE bigtable, fattable;

The same, and also reset any associated sequence generators:

    TRUNCATE bigtable, fattable RESTART IDENTITY;

Truncate the table `othertable`, and cascade to any tables that
reference `othertable` via foreign-key constraints:

    TRUNCATE othertable CASCADE;

Compatibility
=============

The SQL:2008 standard includes a `TRUNCATE` command with the syntax
`TRUNCATE TABLE
   tablename`. The clauses `CONTINUE IDENTITY`/`RESTART IDENTITY` also
appear in that standard, but have slightly different though related
meanings. 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.

See Also
========
