TRUNCATE TABLE Command

PostgreSQL - TRUNCATE TABLE Command

The PostgreSQL TRUNCATE TABLE command is used to quickly remove all rows from a table or a set of tables. It is functionally similar to running a DELETE command on each table without a WHERE clause, but TRUNCATE is significantly faster and more efficient for large tables.

Key differences from DELETE:

• Performance: TRUNCATE does not scan the table row by row, making it much faster.
• Resource Usage: It reclaims disk space immediately, whereas DELETE requires a subsequent VACUUM operation to do so.
• Triggers: TRUNCATE does not fire any ON DELETE triggers that might be defined for the table.
• Transactions: It is transaction-safe. You can roll back a TRUNCATE statement if it’s part of a transaction.

It is important not to confuse TRUNCATE with DROP TABLE. TRUNCATE only removes the data, leaving the table structure intact. DROP TABLE completely removes the table definition, its data, and all associated indexes and constraints.

Syntax

The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE table_name [ , ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];

Optional clauses:

• table_name [ , ... ]: You can truncate multiple tables at once by providing a comma-separated list.
• RESTART IDENTITY: Automatically restarts any sequences tied to columns of the truncated table (e.g., from SERIAL or IDENTITY columns). The next inserted row will start from the sequence’s initial value. This is the default and most common behavior.
• CONTINUE IDENTITY: Does not change the value of any sequences. The next inserted row will use the next value in the sequence as if no truncation occurred.
• CASCADE: Automatically truncates all tables that have foreign-key references to the named tables.
• RESTRICT: Prevents truncation if any other tables have a foreign-key reference to the named table. This is the default.

Example

Let’s use the employees table from the previous example, which has a BIGSERIAL primary key (id).

-- Let's assume the employees table has several records:
SELECT id, full_name FROM employees;

 id |    full_name
----+------------------
  1 | Alice Wonderland
  2 | Bob Builder
  3 | Charlie Chaplin
(3 rows)

Now, let’s truncate the table. The following command removes all rows and resets the id sequence.

testdb=# TRUNCATE TABLE employees RESTART IDENTITY;
TRUNCATE TABLE

The table is now empty. A SELECT query confirms this:

testdb=# SELECT * FROM employees;
 id | full_name | age | department | salary | hire_date
----+-----------+-----+------------+--------+-----------
(0 rows)

Because we used RESTART IDENTITY, the next record we insert will have an id of 1.

testdb=# INSERT INTO employees (full_name, age, department, salary)
         VALUES ('Frank N. Stein', 40, 'R&D', 150000.00) RETURNING id;

 id
----
  1
(1 row)