TRUNCATE
TRUNCATE — Empty a table or set of tables
Synopsis
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 unconditional DELETE on each table, but is faster since it does not actually scan the tables. Additionally, it reclaims disk space immediately rather than requiring a subsequent VACUUM operation. It is most useful on large tables.
Parameters
name
The name of the table to truncate (can be schema-qualified). If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendants (if any) are truncated. Optionally, * can be specified after the table name to explicitly include descendants.
RESTART IDENTITY
Automatically restart sequences owned by columns of the truncated tables.
CONTINUE IDENTITY
Do not change sequence values. This is the default.
CASCADE
Automatically truncate all tables that have foreign key references to any of the named tables, as well as any tables added to the group due to CASCADE.
RESTRICT
Refuse to truncate if any of the tables have foreign key references from tables not listed in the command. This is the default.
Notes
To truncate a table, you must have the TRUNCATE privilege on it.
TRUNCATE requires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on that table. When RESTART IDENTITY is specified, any sequences that need to be restarted are also locked exclusively. If concurrent access to the table is required, the DELETE command should be used instead.
TRUNCATE cannot be used on tables that are referenced by foreign keys from other tables, unless those tables are also truncated in the same command. The feasibility check in these cases requires a table scan, and the primary purpose is not to perform scans. The CASCADE option can be used to automatically include all dependent tables — but use it with great care, otherwise you may lose data! Note in particular that when the table to be truncated is a partition, sibling partitions are not affected, but all referencing tables are cascaded, regardless of whether they are partitioned.
TRUNCATE will not fire any ON DELETE triggers that may exist on the table. However, it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined on any of these tables, all BEFORE TRUNCATE triggers will be fired before any truncation occurs, and all AFTER TRUNCATE triggers will be fired after the last truncation is complete and all sequences have been reset. The triggers are fired in the order in which the tables are processed (first those listed in the command, then those added due to cascading).
TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions that are using a snapshot taken before the truncation occurred.
From the perspective of the data in the table, TRUNCATE is transaction-safe: if the enclosing transaction does not commit, the truncation will be safely rolled back.
When RESTART IDENTITY is specified, the implicit ALTER SEQUENCE RESTART operations are also performed transactionally. That is, if the enclosing transaction does not commit, they will also be rolled back. Note that if additional sequence operations are performed on the restarted sequences before the transaction rolls back, the effects of those operations on the sequences will be rolled back as well, but their effects on currval() will not be rolled back. That is, after the transaction, currval() will continue to reflect the last sequence value obtained within the failed transaction, even though the sequence itself may no longer be consistent with this. This is similar to the usual behavior of currval() after a failed transaction.
TRUNCATE does not currently support foreign tables. This means that if a specified table has any foreign descendants, the command will fail.
Examples
# Truncate tables bigtable and fattable:
TRUNCATE bigtable, fattable;
# Do the same, and also reset any associated sequence generators:
TRUNCATE bigtable, fattable RESTART IDENTITY;
# Truncate table othertable, and cascade to any tables that reference othertable via foreign key constraints:
TRUNCATE othertable CASCADE;