TRUNCATE

Clear all rows in the table.

Summary

TRUNCATE [TABLE] [ONLY] name [ * ] [, ...]
     [ RESTART IDENTITY | CONTINUE IDENTITY ] [CASCADE | RESTRICT]

describe

TRUNCATE Quickly delete all rows in a table or set of tables. It's the same effect as doing an unlimited DELETE on each table, but because it doesn't actually scan the table, it's faster. This is most useful on large tables.

You must have TRUNCATE privileges on the table to clear the table rows.

TRUNCATE acquires access exclusive locks on the table to which it operates, which will prevent all other concurrent operations on that table. If RESTART IDENTITY is specified, all sequences that will be recounted will be exclusively locked. If you need to access the table concurrently, you should use the DELETE command instead.

Parameters

name

The name of the table to be emptied (can be modified with schema). If ONLY is specified before the table name, only the table is cleared. If ONLY is not specified, the table and all its subtables (if any) will be cleared. (Optional) You can specify * after the table name to explicitly indicate that subtables are included.

CASCADE

Because this keyword works for foreign key references (not supported in YMatrix database), it is invalid.

RESTART IDENTITY

Automatically restart the sequence owned by columns in the empty table.

CONTINUE IDENTITY

Do not change the value of the sequence. This is the default value.

RESTRICT

Because this keyword works for foreign key references (not supported in YMatrix database), it is invalid.

Note

TRUNCATE will not run any user-defined ON DELETE triggers that may exist in the table.

TRUNCATE does not clear any tables inherited from the specified table. Only the specified table is emptied, not its child table.

TRUNCATE does not clear any subtables of the partition table. If a subtable of a partitioned table is specified, TRUNCATE does not delete rows from the table and its subtables.

TRUNCATE is not MVCC safe. After clearing, if the concurrent transaction uses a snapshot taken before the clearing occurs, the table will appear empty for the concurrent transaction.

For data in the table, TRUNCATE is transaction-safe: if the transaction belonging to is not committed, the clear will be safely rolled back.

TRUNCATE acquires the ACCESS EXCLUSIVE lock on each table of its operations, which will block all other concurrent operations on that table. If you need to access the table concurrently, you should use the DELETE command instead.

When RESTART IDENTITY is specified, the implicit ALTER SEQUENCE RESTART operations will also be completed transactionally; that is, if the transaction is not committed, they will be rolled back. This is different from the normal behavior of ALTER SEQUENCE RESTART. Note that if any other sequence operations are performed on the restarted sequence before the transaction is rolled back, the impact of these operations on the sequence will be rolled back, but will not have an impact on currval(). That is, after the transaction currval(), it will continue to reflect the last sequence value obtained in the failed transaction, even if the sequence itself may no longer be consistent with this. This is similar to the usual behavior of currval() after a transaction failure.

Example

Clear table films and distributors:

TRUNCATE films, distributors;

Same, and reset all associated sequence generators:

TRUNCATE films, distributors RESTART IDENTITY;

compatibility

SQL: The 2008 standard includes the TRUNCATE command with the syntax TRUNCATE TABLE tablename. The clause CONTINUE IDENTITY/RESTART IDENTITY also appears in this standard, but although it has related meanings, it is slightly different. Some of the concurrent behavior of this command is defined by the standard implementation, so the above considerations should be taken into account and compared with other implementations if necessary.