How to Truncate a Table in PostgreSQL

In PostgreSQL, the TRUNCATE command is used to remove all rows from a table without logging individual row deletions. This makes it faster than using the DELETE command when you need to clear a table quickly.

Syntax

The basic syntax for truncating a table in PostgreSQL is as follows:

TRUNCATE TABLE table_name;

Where table_name is the name of the table you want to truncate.

Example

Let's say you have a table called employees and you want to remove all data from it:

TRUNCATE TABLE employees;

Options with TRUNCATE

The TRUNCATE command in PostgreSQL has several options you can use:

• CASCADE – Automatically truncate all tables that have foreign key references to the target table.
• RESTART IDENTITY – Reset the sequence counters for any serial columns to their initial value.
• ONLY – Truncate only the specified table and not its partitions.

For example, to truncate a table and restart its identity column:

TRUNCATE TABLE employees RESTART IDENTITY;

Benefits of TRUNCATE

Using TRUNCATE is much faster than using DELETE, especially when dealing with large datasets. This is because TRUNCATE doesn't generate individual row delete events, and it doesn't fire any triggers.

When to Use TRUNCATE

You should use TRUNCATE when:

• You want to remove all data from a table quickly.
• You don’t need to track individual row deletions (e.g., no triggers or foreign key constraints that require updating other tables).
• You want to reset any auto-incrementing columns (with the RESTART IDENTITY option).

Considerations

While TRUNCATE is a powerful and efficient command, it has a few limitations:

• TRUNCATE is not transaction-safe, meaning if you truncate a table and then roll back a transaction, the truncation will not be undone.
• TRUNCATE cannot be used on tables that are referenced by foreign keys (unless you use the CASCADE option).