PostgreSQL: TRUNCATE TABLE Statement

In this PostgreSQL post explains how to use the PostgreSQL TRUNCATE TABLE statement with syntax and examples.

Description

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

Syntax

The syntax for the TRUNCATE TABLE statement in PostgreSQL is:

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

Parameters or Arguments

TABLE

Optional. The truncate statement will behave exactly the same whether you specify TABLE or not.

ONLY

Optional. If specified, only the table_name listed will be truncated. If not specified, the table_name and all of its descendant tables will be truncated.

schema_name

The name of the schema that the table belongs to.

table_name

The table that you wish to truncate.

table_name

The table that you wish to truncate.

*

Optional. If specified, the table_name and all of its descendant tables will be truncated.

RESTART IDENTITY

Optional. If specified, all sequences on the truncated tables will be reset.

CONTINUE IDENTITY

Optional. If specified, all sequences on the truncated tables will be continue and not be reset. This is the default behavior.

CASCADE

Optional. If specified, all tables that have a foreign key reference to table_name will be truncated as well.

RESTRICT

Optional. If specified, all tables that have a foreign key reference to table_name will not be truncated unless they were listed in the TRUNCATE TABLE statement. This is the default behavior.

Note

• Before you can truncate a table, you must have the necessary privileges such as TRUNCATE.
• The DELETE triggers for the table do not fire during the truncation.
• You can not truncate a table that is referenced by a foreign key unless all tables in the foreign key relationship are listed in the TRUNCATE command.
• All storage for the truncated rows will be reclaimed without requiring a VACUUM.

Example

In PostgreSQL, truncating a table is a fast way to clear out records from a table because it does not need to scan the table. Truncating a table is also a lot easier than dropping the table and recreating it.

Let's look at an example of how to use the TRUNCATE TABLE statement in PostgreSQL.

For example:

TRUNCATE ONLY products;

This example would truncate the table called products and remove all records from that table.

It would be equivalent to the following DELETE statement in PostgreSQL:

DELETE FROM products;

Both of these statements would result in all data from the products table being deleted and since the ONLY keyword was used in the TRUNCATE command, no descendant tables would be truncated. It is always safer to specify the ONLY keyword so that you don't accidentally truncate descendant tables.

Multiple Tables

Next, let's look at how to truncate multiple tables at once.

For example:

TRUNCATE ONLY products, inventory;

This example would truncate the tables called products and inventory. Again, we have included the ONLY keyword so that no descendant tables are truncated.

Reset Identity Columns

By default, when you truncate a table, the identity columns will continue its numbering from where it left off. You can tell PostgreSQL to automatically reset all identity columns when truncating a table by including the RESTART IDENTITY clause.

For example:

TRUNCATE ONLY inventory
RESTART IDENTITY;

In this example, the identity columns in the inventory table will be reset back to their initial values. This is helpful if you have a primary key field that you want to restart at 1 again.