Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

TRUNCATE

Deletes all of the rows from a table without doing a table scan: this operation is a faster alternative to an unqualified DELETE operation. To execute a TRUNCATE command, you must be the owner of the table or a superuser.

TRUNCATE is much more efficient than DELETE and does not require a VACUUM and ANALYZE. However, be aware that TRUNCATE commits the transaction in which it is run.

Syntax

Copy
TRUNCATE [ TABLE ] table_name

Parameters

TABLE

Optional keyword.

table_name

A temporary or persistent table. Only the owner of the table or a superuser may truncate it.

You can truncate any table, including tables that are referenced in foreign-key constraints.

After truncating a table, run the ANALYZE command against the table. You do not need to vacuum a table after truncating it.

Usage Notes

The TRUNCATE command commits the transaction in which it is run; therefore, you cannot roll back a TRUNCATE operation, and a TRUNCATE command may commit other operations when it commits itself.

Examples

Use the TRUNCATE command to delete all of the rows from the CATEGORY table:

Copy
truncate category;

Attempt to roll back a TRUNCATE operation:

Copy
begin; truncate date; rollback; select count(*) from date; count ------- 0 (1 row)

The DATE table remains empty after the ROLLBACK command because the TRUNCATE command committed automatically.