TRUNCATE: Remove All Data Quickly

The TRUNCATE statement removes all rows from a table quickly.
The table itself stays, with all its columns, but it becomes empty.


Syntax

TRUNCATE TABLE table_name;

Example: Empty the Employees Table

TRUNCATE TABLE employees;

SELECT COUNT(*) FROM employees;

Output:

TRUNCATE TABLE
 count 
-------
     0
(1 row)

All rows are gone, but the employees table still exists. You can insert new rows into it.


TRUNCATE vs DELETE

TRUNCATEDELETE
Removes all rowsCan remove some or all rows
Very fast, even for big tablesSlower for big tables, because it removes rows one by one
No WHERE clauseCan use a WHERE clause

PostgreSQL Tip

In many databases, TRUNCATE cannot be undone. In PostgreSQL, it can be undone if you run it inside a transaction. You will learn transactions at the end of this section.

Also, PostgreSQL will not truncate a table that another table points to with a foreign key. You will learn about foreign keys in the Constraints section.


Tip

  • TRUNCATE has no WHERE clause, so it cannot remove only some rows.
  • Use it only when you really want to empty a table.

Answer the question below to check how TRUNCATE works.