PostgreSQL TRUNCATE TABLE (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, the **TRUNCATE TABLE statement provides a quick and efficient way to remove all data from large tables, **freeing up storage space instantly. Unlike the **DELETE statement, which removes rows one by one and logs each deletion, **TRUNCATE TABLE is optimized for performance, especially with large datasets.

In this article, we will explain the **PostgreSQL TRUNCATE TABLE statement in detail, covering its **syntax, **advantages, and **examples to help you master its usage

What is PostgreSQL TRUNCATE TABLE?

TRUNCATE TABLE in **PostgreSQL is a **data removal command used to **delete all rows from a table without deleting the table structure itself. This command is ideal for **fast data removal when the table structure, along with its **indexes and **constraints, needs to be preserved. Compared to **DELETE, **TRUNCATE TABLE is much faster, as it avoids **row-by-row processing

**Syntax

TRUNCATE TABLE table_name;

Example of Using TRUNC

SELECT TRUNC(5.678, 1);

**Output

This will return 5.6 , truncating the number to **one decimal place without rounding.

Why Use TRUNCATE TABLE?

Using **TRUNCATE TABLE is advantageous when dealing with **large tables that require complete data deletion. Here are some reasons why TRUNCATE TABLE statement is faster than the **DELETE statementfor several reasons:

1. **No Row Scanning: TRUNCATE TABLE removes all rows from a table without scanning it, which significantly reduces the time required for large tables.
2. **Immediate Storage Reclamation: Unlike the **DELETE**statement, which may require a subsequent **VACUUM**operation to reclaim storage, **TRUNCATE TABLE**reclaims storage space immediately.
3. **Less Overhead: **TRUNCATE TABLE**is a more straightforward operation with less overhead compared to DELETE, making it more efficient for large-scale data removal.

Examples of PostgreSQL TRUNCATE TABLE

Let's take a look at some examples of using **TRUNCATE TABLE in PostgreSQL, which will help illustrate how this command effectively clears data while preserving the **table's structure and **optimizing performance for large datasets

**Example 1: Using TRUNCATE TABLE on the 'animals' Table

In our database, we already have a table with data called animals. Let's check if it has any data using the below PostgreSQL statement:

SELECT * FROM animals;

**Output

Assume the query returns several rows of data. To remove all data from the animals table quickly, use the **TRUNCATE TABLE**statement:

TRUNCATE TABLE animals;

Now we verify whether the deletion is complete using the below statement. The result should show that the table is now empty.

SELECT * FROM animals;

**Output

**Example 2: Using TRUNCATE TABLE on the 'galaxy' Table

Similarly, consider another table named galaxy. First, check the data in the table:

SELECT * FROMgalaxy;

**Output

Now we will delete all the data from the table using the below statement:

TRUNCATE TABLE galaxy;

Now we verify whether the deletion is complete using the below statement. The result should confirm that the table is empty.

SELECT * FROM galaxy;

**Output

Important Points About PostgreSQL TRUNCATE TABLE

• **Transactional: In **PostgreSQL, the TRUNCATE **TABLE**statement is transactional. This means that if the **TRUNCATE**operation is part of a transaction, it can be rolled back if necessary.
• **Preserves Table Structure: The TRUNCATE TABLE statement removes all rows, but the table structure, including indexes, remains intact.
• For large tables, **TRUNCATE TABLE**is significantly faster than DELETE.
• **Not for Selective Deletion: **TRUNCATE TABLE**removes all rows from the table, so it is not suitable for selective deletions. Use the **DELETE**statement with a WHERE clause for selective row removal.

Conclusion

The **PostgreSQL TRUNCATE TABLE command is a highly efficient way to delete all data from large tables while preserving the table structure. It offers a faster alternative to DELETE for bulk data removal by avoiding **row-by-row logging and immediately **reclaiming storage space. However, for selective deletions, use **DELETE with a **WHERE clause. Additionally, **TRUNCATE TABLE is transactional, allowing it to be safely rolled back within a **transaction if needed, adding flexibility to its usage in **data management.