PostgreSQL TRUNCATE TABLE

Summary: in this tutorial, you will learn how to use PostgreSQL TRUNCATE TABLE statement to quickly delete all data from large tables.

Introduction to PostgreSQL TRUNCATE TABLE statement

To remove all data from a table, you use the DELETE statement. However, when you use the DELETE statement to delete all data from a table that has a lot of data, it is not efficient. In this case, you need to use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;Code language: SQL (Structured Query Language) (sql)

The  TRUNCATE TABLE statement deletes all data from a table without scanning it. This is the reason why it is faster than the DELETE statement.

In addition, the TRUNCATE TABLE statement reclaims the storage right away so you do not have to perform a subsequent VACUMM operation, which is useful in the case of large tables.

Remove all data from one table

The simplest form of the  TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name;Code language: SQL (Structured Query Language) (sql)

The following example uses the TRUNCATE TABLE statement to delete all data from the invoices table:

TRUNCATE TABLE invoices;Code language: SQL (Structured Query Language) (sql)

Besides removing data, you may want to reset the values in the identity column by using the RESTART IDENTITY option like this:

TRUNCATE TABLE table_name 
RESTART IDENTITY;Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes all rows from the invoices table and resets the sequence associated with the  invoice_no column:

TRUNCATE TABLE invoices 
RESTART IDENTITY;Code language: SQL (Structured Query Language) (sql)

By default, the  TRUNCATE TABLE statement uses the CONTINUE IDENTITY option. This option basically does not restart the value in sequence associated with the column in the table.

Remove all data from multiple tables

To remove all data from multiple tables at once, you separate each table by a comma (,) as follows:

TRUNCATE TABLE 
    table_name1, 
    table_name2,
    ...;Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes all data from invoices and customers tables:

TRUNCATE TABLE invoices, customers;Code language: SQL (Structured Query Language) (sql)

Remove all data from a table that has foreign key references

In practice, the table you want to truncate often has the foreign key references from other tables that 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 a table and other tables that have foreign key reference the table, you use CASCADE option in the TRUNCATE TABLE statement as follows :

TRUNCATE TABLE table_name 
CASCADE;Code language: SQL (Structured Query Language) (sql)

The following example deletes data from the invoices table and other tables that reference the invoices table via foreign key constraints:

TRUNCATE TABLE invoices CASCADE;Code language: SQL (Structured Query Language) (sql)

The CASCADE option should be used with further consideration or you may potentially delete data from tables that you did not want.

By default, the TRUNCATE TABLE statement uses the RESTRICT option which prevents you from truncating the table that has foreign key constraint references.

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 the 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. It means that if you place it within a transaction, you can roll it back safely.

Summary

• Use the TRUNCATE TABLE statement to delete all data from a large table.
• Use the CASCADE option to truncate a table and other tables that reference the table via foreign key constraint.
• The TRUNCATE TABLE does not fire ON DELETE trigger. Instead, it fires the BEFORE TRUNCATE and AFTER TRUNCATE triggers.
• The TRUNCATE TABLE statement is transaction-safe.