This tutorial shows you how to use PostgreSQL TRUNCATE TABLE statement to remove all data from large tables quickly.
To remove all data from a table, you use DELETE statement. However, for a large table, it is more efficient to use TRUNCATE TABLE statement.
The TRUNCATE TABLE statement removes all rows from a table without scanning the table. This is the reason why it is faster than the DELETE statement. In addition, it reclaims the storage right away so you don’t have to perform a subsequent VACUMM operation, which is useful in case of large tables.
Remove all data from one table
The simplest form of the TRUNCATE TABLE statement is as follows:
1 | TRUNCATE TABLE table_name; |
For example, to remove all rows from the invoicetable, you use the following statement:
1 | TRUNCATE TABLE invoice; |
PostgreSQL TRUNCATE TABLE statement allows you to not only remove all data from a table but also reset associated sequence generator by specifying RESET IDENTITY option as follows:
1 | TRUNCATE TABLE table_name RESET IDENTITY; |
For example, to remove all rows from the invoicetable and reset sequence associated with the invoice_no column, you use the following statement:
1 | TRUNCATE TABLE invoice RESET IDENTITY; |
By default, the TRUNCATE TABLE statement does not reset the associated sequence. You can use CONTINUE IDENTITY option explicitly in the statement to retain the sequence. However, this is not necessary.
Remove all data from multiple tables
To remove all data from multiple tables at once, you separate each table by a comma as follows:
1 | TRUNCATE TABLE table_name1, table_name2, … |
For example, to remove all data from invoiceand customertables, you use the following statement:
1 | TRUNCATE TABLE invoice, customer; |
Remove all data from table that has foreign key references
In practical, the table you want to truncate often has foreign-key references from other tables which are not listed in the TRUNCATE TABLE statement. By default, the TRUNCATE TABLE statement does not remove any data from the table that has foreign key references.
To remove data from the main table and all tables that have foreign key references to the main table, you use CASCADEoption as follows:
1 | TRUNCATE TABLE table_name CASCADE; |
For example, to remove all data from the invoice table and cascade to any tables that reference to invoice table via foreign key constraints, you use the following statement:
1 | TRUNCATE TABLE invoice CASCADE; |
You should be careful when you use the CASCADEoption, or else you might potentially remove data from tables that you did not intend to.
PostgreSQL TRUNCATE TABLE and ON DELETE trigger
Even though the TRUNCATE TABLE statement remove all data from a table, it does not fire any ON DELETE triggers associated with that table.
To fire trigger when the TRUNCATE TABLE command applied to a table, you must define BEFORE TRUNCATE and/or AFTER TRUNCATE triggers in that table.
PostgreSQL TRUNCATE TABLE and transaction
The TRUNCATE TABLE is transaction-safe, which means if you put it within transaction statements such as BEGIN … ROLLBACK, the truncation operation will be rolled back safely.
In this tutorial, we have shown you how to remove all data from large tables quickly and efficiently by using the TRUNCATE TABLE statement. In addition, we introduced you to some other options that the statement provides.