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
| TRUNCATE | DELETE |
|---|---|
| Removes all rows | Can remove some or all rows |
| Very fast, even for big tables | Slower for big tables, because it removes rows one by one |
| No WHERE clause | Can 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
TRUNCATEhas noWHEREclause, 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.