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 invoice table, 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 invoice table 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 invoice and customer tables, you use the following statement:
1 | TRUNCATE TABLE invoice, customer; |
Remove all data from table that has foreign key references
In practice, the table you want to truncate often has the 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 CASCADE option 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 CASCADE option, or else you might potentially delete data from the tables that you did not intend to.
PostgreSQL TRUNCATE TABLE and ON DELETE trigger
Even though the TRUNCATE TABLE statement removes all data from a table, it does not fire any ON DELETE triggers associated with that table.
To fire the trigger when the TRUNCATE TABLE command applied to a table, you must define BEFORE TRUNCATE and/or AFTER TRUNCATE triggers for that table.
PostgreSQL TRUNCATE TABLE and transaction
The TRUNCATE TABLE is transaction-safe, which means if you place it within the 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.