TRUNCATE

You can use TRUNCATE within an explicit transaction or stored procedure to make other related changes to data in one atomic unit. When you use TRUNCATE in this manner:

• Except in the case where the table being truncated was created in the same transaction, the table storage is not freed (made available for reuse) until the truncating transaction commits.
• The truncating transaction can be rolled back, in which case the table storage and contents will remain intact.
• The table is locked, and no concurrent access (including SELECT commands) to the table from other sessions or transactions is permitted until the truncating transaction commits or rolls back. The rollback could happen as a result of a ROLLBACK command or an execution failure in the transaction.
• If the truncating transaction accesses other tables, there is an increased likelihood of deadlock. You can avoid this problem issuing LOCK TABLE <tablename> IN ACCESS EXCLUSIVE MODE at the beginning of any transaction or procedure that truncates a table or tables. See LOCK TABLE for guidelines on the use of that command.
• TRUNCATE TABLE is much faster than deleting all rows with DELETE FROM <table> and also does not require a subsequent GROOM TABLE to reclaim the space. But it does incur a small risk that in some cases, concurrent transactions attempting to access and truncate the same table could see the message ERROR: Cannot access table because of concurrent truncateor ERROR: Cannot truncate table because of concurrent insert or update. You can minimize the risk by using the LOCK TABLE command and by keeping a transaction that truncates one or more tables active for no longer than is necessary to achieve its goal.

Syntax for truncating an empty table:

TRUNCATE [ TABLE ] <name>

The TRUNCATE command takes the following input:

The TRUNCATE command has the following output:

The following provides sample usage.

• Remove all rows from the table bigtable:

  MYDB.SCH1(USER)=> TRUNCATE  bigtable;

• Replace the contents of the table mytable with new data:

  BEGIN;
  TRUNCATE TABLE mytable;
  INSERT INTO mytable SELECT …;
  COMMIT;