Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgresql
communitycreator

What is the truncate table command in PostgreSQL?

Fatima Hasan

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

The TRUNCATE TABLE command in PostgreSQL is used to delete all the rows in a table. It is a DDLData Definition Language command, and is more efficient than using the DELETE command because it does not need to scan the table. Instead, the TRUNCATE TABLE simply drops the entire table and then just recreates the structure.

The DELETE command is useful in cases when only certain data, that matches some conditions, need to be deleted. This command scans the table row-by-row to find all the contents that match the specified condition.

The TRUNCATE TABLE command deletes all the data from a table in one go. It does not even need to perform a vacuum operation afterward as the storage is reclaimed immediately. With this command, a WHERE clause cannot be specified.

Syntax

To remove the data from one table, the following command is used:

TRUNCATE TABLE table_name;

After executing the above command, if you try to select any data from the table, it will not return any rows.

To remove the data from multiple tables, the syntax is as follows:

TRUNCATE TABLE table1, table2, table3;

The table contents that you wish to delete may have some foreign key references and, by default, this data is not deleted with the TRUNCATE TABLE command. To remove such data, you need to explicitly mention it using the CASCADE keyword:

TRUNCATE TABLE table_name
CASCADE;
This deletes the data from the table table_name and also any other tables which have referenced this data.

Triggers

This command does not fire the ON DELETE triggers even though it does delete the data. Only BEFORE/AFTER TRUNCATE triggers may be fired if defined.

Transactions

TRUNCATE TABLE is transaction safe so, if it is performed inside a transaction that is rolled back, the data is also rolled back.

RELATED TAGS

postgresql
communitycreator

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring