Empties a table or set of tables of all rows.
Greenplum Database does not enforce referential integrity syntax (foreign key constraints).
TRUNCATEtruncates a table that is referenced in a foreign key constraint even if the
CASCADEoption is omitted.
TRUNCATE [TABLE] [ONLY] <name> [ * ] [, ...]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [CASCADE | RESTRICT]
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.
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, you can specify
* after the table name to explicitly indicate that descendant tables are included.
TRUNCATE acquires an
ACCESS EXCLUSIVE lock on each table that 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 you require concurrent access to a table, use the
DELETE command instead.
TRUNCATE will not fire any user-defined
ON DELETE triggers that might exist for the tables.
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.
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.
TRUNCATE is not currently supported for foreign tables. This implies that if a specified table has any descendant tables that are foreign, the command will fail.
Empty the tables
TRUNCATE films, distributors;
The same, and also reset any associated sequence generators:
TRUNCATE films, distributors RESTART IDENTITY;
The SQL:2008 standard includes a
TRUNCATE command with the syntax
TRUNCATE TABLE tablename. The clauses
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.
Parent topic: SQL Commands