TRUNCATE - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).


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 run a TRUNCATE command, you must be have the TRUNCATE TABLE permission, be the owner of the table, or a superuser. To grant permissions to truncate a table, use the GRANT command.

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


TRUNCATE [ TABLE ] table_name

The command also works on a materialized view.

TRUNCATE materialized_view_name



Optional keyword.


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.

You don't need to vacuum a table after truncating it.


A materialized view.

You can truncate a materialized view that is used for Streaming ingestion.

Usage notes

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


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

truncate category;

Attempt to roll back a TRUNCATE operation:

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.

The following example uses the TRUNCATE command to delete all of the rows from a materialized view.

truncate my_materialized_view;

It deletes all records in the materialized view and leaves the materialized view and its schema intact. In the query, the materialized view name is a sample.