Truncate Tables in PostgreSQL

Use the TRUNCATE TABLE command to delete all the data from the specified table in PostgreSQL Database.

• To DELETE command will also delete all data from the table, but it may take more time if a table being deleted has a large amount of data. The TRUNCATE command deletes large amount of data faster than the DELETE command.
• The TRUNCATE command deletes all rows without scanning the rows, so it's faster than the DELETE statement.
• TRUNCATE statement frees up space occupied by rows immediately rather than performing subsequent VACUUME operations.

Syntax:

TRUNCATE [TABLE] <table_name>

The following command will remove all the data from the employee table.

TRUNCATE TABLE employee;
--or 
TRUNCATE employee;

Along with removing all data from a table, to reset identity column values, use the RESTART IDENTITY parameter.

The following command will remove all data from the employee table and reset the sequence associated with emp_id column.

TRUNCATE TABLE employee RESTART IDENTITY;

If a table you are trying to truncate a table that has a foreign key reference from another table, then by default TRUNCATE statement will not allow you to truncate a table and will raise an error.

To remove data from the parent table and all of its child tables (having foreign key references with parent table), you need to use the CASCADE option along with the TRUNCATE statement.

For example, the following command will remove all rows from the department table and all other tables that have references to the department table through foreign key constraints.

TRUNCATE TABLE department CASCADE;

We can also use RESTART IDENTITY with CASCADE to reset the identity column value.

TRUNCATE TABLE department RESTART IDENTITY CASCADE;

Note: the TRUNCATE with CASCADE option should be used very carefully, as you might end up removing data from the tables that you are not intended to.

Remove data from multiple tables:

PostgreSQL allows using the TRUNCATE command to delete all data from multiple tables. Specify the comma-separated table names with the TRUNCATE TABLE command.

TRUNCATE TABLE employee, department;