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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

TRUNCATE TABLE table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

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

SELECT COUNT(*) FROM transactions;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 count
-------
     0Code language: plaintext (plaintext)

Truncating a Table with Foreign Key References #

First, truncate the categories table:

TRUNCATE TABLE categories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.Code language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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"Code language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert a new row into the brands table:

INSERT INTO brands(brand_name) 
VALUES('Apple') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id | brand_name
----------+------------
       11 | AppleCode language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id | brand_name
----------+------------
        1 | AppleCode language: plaintext (plaintext)

Finally, insert a new row into the brands table:

INSERT INTO brands(brand_name) 
VALUES('Apple') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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, ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

TRUNCATE TABLE users, profiles CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Notice

NOTICE:  truncate cascades to table "transactions"Code language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 #