TRUNCATE

TRUNCATE
Prev	Up	SQL Commands	Home	Next

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 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 — + 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 Section 13.5 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. +

Prev	Up	Next
START TRANSACTION	Home	UNLISTEN