PostgreSQL TRUNCATE TABLE

Overview:

The TRUNCATE TABLE command in PostgreSQL is used to delete all rows from a table efficiently, without logging individual row deletions. It is faster than the DELETE command as it does not generate individual row delete logs.

Basic TRUNCATE Example

Example 1:

To truncate a table named employees, use the following command:


TRUNCATE TABLE employees;

This command will remove all records from the employees table instantly.

Truncate with CASCADE

Example 2:

Using CASCADE, you can truncate a table and automatically truncate all tables that have foreign key references to it:


TRUNCATE TABLE employees CASCADE;

This will truncate the employees table and any table that has a foreign key reference to it.

Truncate with RESTART IDENTITY

Example 3:

The RESTART IDENTITY option resets the sequence associated with the table columns:


TRUNCATE TABLE employees RESTART IDENTITY;

This command will truncate the employees table and reset any serial columns back to their start values.

Truncate Multiple Tables

Example 4:

You can truncate multiple tables in a single command:


TRUNCATE TABLE employees, departments;

This will truncate both the employees and departments tables.

Truncate with CONTINUE IDENTITY

Example 5:

The CONTINUE IDENTITY option preserves the current identity values:


TRUNCATE TABLE employees CONTINUE IDENTITY;

This will truncate the employees table but keep the current sequence values intact.

Truncate and Performance

Example 6:

The TRUNCATE operation is generally faster than deleting records one by one, due to less logging and transaction overhead.


TRUNCATE TABLE large_table;

This command is ideal for quickly clearing large tables.

Truncate and Transactions

Example 7:

The TRUNCATE command is transaction-safe, meaning it can be rolled back if used within a transaction block.


BEGIN;
TRUNCATE TABLE employees;
ROLLBACK;

This example shows how to use TRUNCATE within a transaction and roll it back, restoring the original data.

Truncate and Privileges

Example 8:

Only users with the appropriate privileges can execute the TRUNCATE command.


GRANT TRUNCATE ON employees TO user_name;

This command grants the TRUNCATE privilege on the employees table to a specific user.

Truncate and Foreign Keys

Example 9:

When using TRUNCATE, ensure there are no foreign key constraints unless using CASCADE.


TRUNCATE TABLE employees CASCADE;

This ensures that all foreign key constraints are respected by truncating dependent tables as well.

Truncate and Vacuum

Example 10:

Running VACUUM after a TRUNCATE operation can help reclaim storage space.


TRUNCATE TABLE employees;
VACUUM;

This combination ensures that space used by the truncated table is efficiently reclaimed.