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 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.

Syntax

TRUNCATE [ TABLE ] table_name

The command also works on a materialized view.

TRUNCATE materialized_view_name

Parameters

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.

Examples

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.