PostgreSQL TRUNCATE TABLE Statement

Summary: In this tutorial, you’ll learn how to use the PostgreSQL TRUNCATE TABLE statement to quickly and efficiently remove all data from large tables.

Getting started with the PostgreSQL TRUNCATE TABLE Statement #

The TRUNCATE TABLE statement allows you to remove all rows from a table. Here’s the basic syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;Copy

In this syntax, you provide the table name you want to truncate in the TRUNCATE TABLE clause.

Note that the TABLE keyword is optional, so you can make the statement shorter like this:

TRUNCATE table_name;Copy

Truncating Table with Identity Columns #

If the table you want to truncate has identity columns, you can use the RESTART IDENTITY option to restart the values of the identity columns:

TRUNCATE TABLE table_name 
RESTART IDENTITY;Copy

For example, if the value in the identity column starts at 1 with an increment of 1, you can use the RESTART IDENTITY option to restart the value back to 1.

If you don’t want to restart the values of the identity columns, you can use the CONTINUE IDENTITY option:

TRUNCATE TABLE table_name 
CONTINUE IDENTITY;Copy

Since the CONTINUE IDENTITY option is the default, you can ignore it to use the continuing identity values:

TRUNCATE TABLE table_name;Copy

Truncating Table with Foreign Key References #

If you truncate a table with foreign key references, you’ll get an error. Fortunately, you can use the CASCADE option to automatically truncate the table as well as its foreign key tables in one go:

TRUNCATE TABLE table_name 
CASCADE;Copy

To reject the truncation of the foreign key tables, you can ignore the CASCADE option or explicitly use the RESTRICT option:

TRUNCATE TABLE table_name 
RESTRICT;Copy

Note that the RESTRICT option is the default.

If you use the RESTART IDENTITY and CASCADE options at the same time, you need to place the CASCADE option after the RESTART IDENTITY option:

TRUNCATE TABLE table_name
RESTART IDENTITY
CASCADE;Copy

PostgreSQL TRUNCATE TABLE Statement Examples #

We’ll use the inventory database for demonstration purposes.

Truncating One Table #

First, use the TRUNCATE TABLE to delete all data from the transactions table:

TRUNCATE TABLE transactions;Copy

Try it

Second, verify the contents of the truncation by counting rows from the transactions table:

SELECT COUNT(*) FROM transactions;Copy

Try it

Output:

 count
-------
     0Copy

Truncating a Table with Foreign Key References #

First, truncate the categories table:

TRUNCATE TABLE categories;Copy

Try it

Error:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "products" references "categories".
HINT:  Truncate table "products" at the same time, or use TRUNCATE ... CASCADE.Copy

It returns an error because a foreign key constraint in the products table references the categories table.

Second, truncate the categories table with the CASCADE option:

TRUNCATE TABLE categories CASCADE;Copy

Try it

PostgreSQL issued the following notices:

NOTICE:  truncate cascades to table "products"
NOTICE:  truncate cascades to table "product_tags"
NOTICE:  truncate cascades to table "inventories"
NOTICE:  truncate cascades to table "transactions"Copy

The TRUNCATE TABLE statement truncates the products table and has a foreign key reference to the categories table.

Since the products table also has foreign key references from the other three tables, product_tags, inventories, and transactions, the TRUNCATE TABLE statement also truncates these dependent tables.

Restarting Identity Option #

The brands table has the id as the identity column. We’ll truncate this table using the default and the RESTART IDENTITY options.

First, truncate the brands table:

TRUNCATE TABLE brands CASCADE;Copy

Try it

Second, insert a new row into the brands table:

INSERT INTO brands(brand_name) 
VALUES('Apple') 
RETURNING *;Copy

Try it

Output:

 brand_id | brand_name
----------+------------
       11 | AppleCopy

PostgreSQL uses the next identity value, which is the default behavior.

Third, truncate the brands table again with the RESTART IDENTITY option:

TRUNCATE TABLE brands 
RESTART IDENTITY CASCADE;Copy

Try it

Output:

 brand_id | brand_name
----------+------------
        1 | AppleCopy

Finally, insert a new row into the brands table:

INSERT INTO brands(brand_name) 
VALUES('Apple') 
RETURNING *;Copy

Try it

The value in the id column starts at 1.

PostgreSQL TRUNCATE TABLE Statement – Truncating Multiple Tables #

You can truncate multiple tables in one go using the TRUNCATE TABLE statement:

TRUNCATE TABLE table1, table2, ...;Copy

For example, the following statement truncates both users and profiles tables:

TRUNCATE TABLE users, profiles CASCADE;Copy

Notice

NOTICE:  truncate cascades to table "transactions"Copy

Since the transactions table has a foreign key reference to the users table, the TRUNCATE TABLE statement also truncated the transactions table.

TRUNCATE TABLE vs. DELETE statement #

The DELETE statement without a WHERE clause also allows you to remove all data from a table:

DELETE FROM table_name;Copy

When the table has a lot of data, the DELETE statement is not efficient for the following reasons:

• Row-by-row deletion: The DELETE statement needs to delete each row individually. For each row, it needs to check constraints and update the indexes, which can be slow if the table has lot of rows.
• Transaction logging: The DELETE statement logs each row in the transaction log to support rollback operations. This logging may create significant overhead when deleting a large number of rows.
• Locking: The DELETE statement acquires row-level locks, which causes performance bottlenecks.

The TRUNCATE TABLE statement addresses these issues by:

• Faster operation: The TRUNCATE TABLE statement removes all rows by deallocating the data pages, which is faster than removing rows one by one.
• Minimal logging: The TRUNCATE TABLE statement generates minimal transaction log data compared to the DELETE statement, reducing logging overhead.
• No row-level locks: The TRUNCATE TABLE statement can perform faster because it does not acquire row-level locks.

Summary #

• Use the PostgreSQL TRUNCATE TABLE statement to delete all rows from large tables quickly and efficiently.
• Use the CASCADE option to truncate cascade to tables with foreign key references to the table you want to truncate.
• Use the RESTART IDENTITY option to restart the values in the identity column.

Quiz #

Quiz

PostgreSQL TRUNCATE TABLE Statement

5 questions

To you understand how to use the PostgreSQL TRUNCATE TABLE statement to quickly and efficiently remove all data from large tables.

Start quiz

Was this helpful?

YesNo