PostgreSQL - TRUNCATE TABLE

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 statement for 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 * FROM galaxy;

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.