PostgreSQL – TRUNCATE TABLE
PostgreSQL supports the TRUNCATE TABLE statement to remove all data from large tables quickly. To remove all data from a table, you use the DELETE statement. However, for a large table, it is more efficient to use the TRUNCATE TABLE statement. The TRUNCATE TABLE statement removes all rows from a table without scanning it. This is the reason why it is faster than the DELETE statement. In addition, the TRUNCATE TABLE statement reclaims the storage right away so the user does not have to perform a subsequent VACUUM operation, which is useful in case of large tables.
Syntax: TRUNCATE TABLE table_name;
Example 1:
In our database, we already have a table with data called animals
. Let’s check if it has any data using the below statement:
SELECT * FROM animals;
It shows the following result:
Now we will delete all the data from the table using the below statement:
TRUNCATE TABLE animals;
Now we verify whether the deletion is complete using the below statement:
SELECT * FROM animals;
Output:
Example 2:
In our database, we already have a table with data called galaxy
. Let’s check if it has any data using the below statement:
SELECT * FROM galaxy;
It shows the following result:
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:
SELECT * FROM galaxy;
Output:
Contact Us