PostgreSQL TRUNCATE TABLE statement

Preamble

​​

PostgreSQL TRUNCATE TABLE statement is used to remove (truncate) all records from a table or set of tables in PostgreSQL. It performs the same function as the DELETE operator, without the WHERE operator.

Warning: If you truncate a table, the TRUNCATE TABLE operator cannot be overridden if it is not in a transaction that was not committed.

The syntax for TRUNCATE TABLE statement in PostgreSQL

TRUNCATE [TABLE] [ONLY] [schema_name.]table_name [*] [,...]
[ RESTART IDENTITY | CONTINUE IDENTITY ]
[ CASCADE | LIMIT] ;

Parameters and arguments of the statement

• TABLE – Optional. The truncate operator will behave identically regardless of whether you specified a TABLE or not.
• ONLY – Optional. If specified, the only table_name will be truncated. If not specified, table_name and all its child tables will be truncated.
• schema_name – The name of the scheme to which the table belongs.
• Table_name – The table that you want to be truncated.
• * – Optional. If specified, table_name and all his child tables will be truncated.
• RESTART IDENTITY – Optional. If specified, all sequences in the truncated tables will be reset.
• CONTINUE IDENTITY – Optional. If specified, all the sequences in the truncated tables will be left and will not be reset. This is the default behavior.
• CASCADE – Optional. If specified, all tables that have an external key reference to table_name will also be truncated.
• RESTRICT – Optional. If specified, all tables that have an external key reference to table_name will not be truncated if they were not listed in the TRUNCATE TABLE operator. This is the default behavior.

Note:

• Before you can truncate the table, you must have the necessary privileges such as TRUNCATE.
• DELETE triggers for a table do not start during truncation.

You cannot truncate a table that is referenced by a foreign key unless the TRUNCATE command lists all tables in relation to the foreign key.

The entire truncated row storage will be restored without VACUUM.

In PostgreSQL, the truncation of a table is a quick way to remove records from a table because you don’t need to scan the table to do so. Truncation of a table is also much easier than removing a table and recreating it.

An example of using the TRUNCATE TABLE operator in PostgreSQL

For example:

TRUNCATE ONLY products;

In this example, the table with the name of the products will be truncated and all records from this table will be deleted.

This would be equivalent to the next DELETE operator in PostgreSQL

DELETE FROM products;

Both of these operators will remove all data from the products table, and since the ONLY keyword was used in the TRUNCATE command, no descendant tables will be truncated. It is always safer to specify the ONLY keyword to avoid accidentally truncating the descendant tables.

Several tables

Next, let’s look at how to truncate several tables at once.
For example:

TRUNCATE ONLY products, inventory;

In this example, the tables with the names of products and inventory will be truncated. Again, we have enabled the ONLY keyword so that no descendant tables are truncated.

Resetting columns of identifiers

By default, when you truncate a table, the ID columns continue numbering from where it stopped. You can specify PostgreSQL to automatically reset all identifier columns when you truncate a table by enabling the RESTART IDENTITY option.
For example:

TRUNCATE ONLY inventory

RESTART IDENTITY;

In this example, the identifier columns in the inventory table will return to their original values. This is useful if you have a primary key field that you want to restart with 1.

PostgreSQL Tutorial for Beginners; PostgreSQL TRUNCATE TABLE Statement

Enteros

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.