Introduction to PostgreSQL TRUNCATE TABLE statement

To remove all data from a table, you use theDELETE statement without aWHERE clause. However, when the table has numerous data, theDELETE statement is not efficient. In this case, you can use theTRUNCATE TABLE statement.

TheTRUNCATE TABLE statement deletes all data from a table very fast. Here’s the basic syntax of theTRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

In this syntax, you specify the name of the table that you want to delete data after the TRUNCATE TABLE keywords.

Remove all data from multiple tables

To remove all data from multiple tables at once, you separate the tables by commas (,) as follows:

TRUNCATE TABLE    table_name1,    table_name2,    ...;

In this syntax, you specify the name of the tables that you want to delete all data after theTRUNCATE TABLE keywords.

Remove all data from a table that has foreign key references

In practice, the table you want to delete all data often hasforeign key references from other tables.

By default, theTRUNCATE 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 references the table, you useCASCADE option in theTRUNCATE TABLE statement as follows :

TRUNCATE TABLE table_nameCASCADE;

PostgreSQL TRUNCATE TABLE statement examples

Let’s explore some examples of using theTRUNCATE TABLE statement.

1) Basic PostgreSQL TRUNCATE TABLE statement example

First, create a new table calledproducts:

CREATE TABLE products(    idSERIAL PRIMARY KEY,    name VARCHAR(255)NOT NULL,    priceDECIMAL(10,2)NOT NULL DEFAULT 0);

Second, insert some rows into theproducts table:

INSERT INTO products (name, price)VALUES    ('A',19.99),    ('B',29.99),    ('C',39.99),    ('D',49.99)RETURNING*;

Output:

id | name | price----+------+-------  1 | A    | 19.99  2 | B    | 29.99  3 | C    | 39.99  4 | D    | 49.99(4 rows)

Third, delete all data from theproducts table using theTRUNCATE TABLE statement:

TRUNCATE TABLE products;

Output:

TRUNCATE TABLE

2) Using PostgreSQL TRUNCATE TABLE statement to delete all data from multiple tables

First, create a table calledcustomers and insert data into it:

CREATE TABLE customers(   id SERIAL PRIMARY KEY,   name VARCHAR(255)NOT NULL,   phone VARCHAR(25)NOT NULL);INSERT INTO customers (name,phone) VALUES    ('John Doe', '123-456-7890'),    ('Jane Smith', '987-654-3210'),    ('Robert Johnson', '555-123-4567')RETURNING *;

Second, create a table calledvendors and insert data into it:

CREATE TABLE vendors(   idSERIAL PRIMARY KEY,   name VARCHAR(255)NOT NULL,   phoneVARCHAR(25)NOT NULL);INSERT INTO vendors (name, phone)VALUES    ('ABC Electronics','555-123-4567'),    ('XYZ Supplies','999-888-7777'),    ('Tech Solutions Inc.','111-222-3333')RETURNING*;

Third, delete data from the customers and vendors tables using the TRUNCATE TABLE statement:

TRUNCATE TABLE customers, vendors;

3) Using PostgreSQL TRUNCATE TABLE statement to delete data from a table referenced by a foreign key

First, create tablesorders andorder_details:

CREATE TABLE orders(  order_idSERIAL PRIMARY KEY,  customer_nameVARCHAR(255)NOT NULL,  ordered_dateDATE NOT NULL,  status VARCHAR(20)NOT NULL);CREATE TABLE order_items (  order_idINT NOT NULL,  item_idINT NOT NULL,  product_nameVARCHAR(255)NOT NULL,  quantityINT NOT NULL,  FOREIGN KEY (order_id)     REFERENCES orders(order_id)ON DELETE CASCADE ON UPDATE CASCADE,  PRIMARY KEY (order_id, item_id));INSERT INTO orders (customer_name, ordered_date,status)VALUES  ('John Doe','2024-01-25','Processing'),  ('Jane Smith','2024-01-26','Shipped'),  ('Bob Johnson','2024-01-27','Delivered');INSERT INTO order_items (order_id, item_id, product_name, quantity)VALUES  (1,1,'A',2),  (1,2,'B',1),  (2,1,'C',3),  (3,1,'D',5),  (3,2,'E',2);

Second, attempt to truncate data from theorders table:

TRUNCATE TABLE orders;

PostgreSQL issues the following error:

DETAIL:Table "order_items" references "orders".HINT:Truncate table "order_items" at the sametime,or use TRUNCATE ... CASCADE.

The reason is that theorders table is referenced by theorder_items table. To truncate both theorders andorder_items tables at the same time, you can use theCASCADE option.

Third, truncate data from bothorders andorder_items tables:

TRUNCATE TABLE orders CASCADE;

PostgreSQL issues the following notice indicating that theorder_items is also truncated:

NOTICE:truncate cascadesto table "order_items"TRUNCATE TABLE

Note that theTRUNCATE TABLE statement uses theRESTRICT option by default to prevent a table that is referenced by a foreign key from being truncated.

Restarting sequence

Besides removing data, you may want to reset the values of theidentity column by using theRESTART IDENTITY option like this:

TRUNCATE TABLE table_nameRESTART IDENTITY;

For example, the following statement removes all rows from theproducts table and resets thesequence associated with the id column:

TRUNCATE TABLE productsRESTART IDENTITY;

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

TRUNCATE TABLE statement and ON DELETE trigger

Even though theTRUNCATE TABLE statement removes all data from a table, it does not fire anyON DELETEtriggers associated with the table.

To fire the trigger when the TRUNCATE TABLE statement executes, you need to define BEFORE TRUNCATE and/or AFTER TRUNCATE triggers for that table.

TRUNCATE TABLE statement and transactions

TheTRUNCATE TABLE is transaction-safe, meaning that you can place it within a transaction.

Why TRUNCATE TABLE statement is more efficient than the DELETE statement

TheTRUNCATE TABLE statement is more efficient than theDELETE statement due to the following main reasons:

• Minimal logging: TheTRUNCATE TABLE statement doesn’t generate individual row deletion logs. Instead, it deallocates entire data pages making it faster than theDELETE statement.
• Fewer resources: The truncate operation is more lightweight than the delete option because it doesn’t generate as much undo and redo information. It releases storage space without scanning individual rows.
• Lower-level locking mechanism: The truncate operation often requires lower-level locks and is less prone to conflicts with other transactions, which improves overall system concurrency.

Summary

• Use theTRUNCATE TABLE statement to delete all data from a large table very fast.
• Use theCASCADE option to truncate a table that is referenced by foreign key constraints.
• TheTRUNCATE TABLE deletes data but does not fireON DELETE triggers. Instead, it fires theBEFORE TRUNCATE andAFTER TRUNCATE triggers.
• TheTRUNCATE TABLE statement is transaction-safe.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL TRUNCATE TABLE statement
• PostgreSQL TRUNCATE TABLE statement examples
• Restarting sequence
• TRUNCATE TABLE statement and ON DELETE trigger
• TRUNCATE TABLE statement and transactions
• Why TRUNCATE TABLE statement is more efficient than the DELETE statement
• Summary