PostgreSQL TRUNCATE TABLE - Empty tables

This article will describes how to use the TRUNCATE TABLE statement to empty one or more tables.

If you need to clear all rows in a table, you can use TRUNCATE TABLE statement or DELETE * FROM table.

The TRUNCATE statement has the same effect as a DELETE statement without a WHERE clause. But TRUNCATE is faster because it doesn’t scan the table. Also, disk space is reclaimed immediately after TRUNCATE. TRUNCATE is very Useful when emptying large tables.

PostgreSQL TRUNCATE syntax

To remove all rows from one or more tables, use the using TRUNCATE statement:

TRUNCATE [TABLE] [ONLY] table_name [ * ] [, ... ]
    [RESTART IDENTITY | CONTINUE IDENTITY] [ CASCADE | RESTRICT ]

Explanation:

  • The TABLE Keywords are optional.
  • The ONLY Keywords are optional. If ONLY is specified before the table name, only this table will be truncated. Otherwise, this table and all descendant child tables will be truncated.
  • The table_name is the name of the table to be truncated. The * after the table name indicates its descendant tables will also be truncated explicitly.
  • You can empty multiple tables in one TRUNCATE statement. Multiples table names are separated by commas.
  • The RESTART IDENTITY option is used to reset the sequence owned by the column in the table automatically. The CONTINUE IDENTITY option indicates to continue the sequence in the table, which is the default.
  • The CASCADE option indicates to truncate all tables that have foreign-key references to any of the named tables automatically. The RESTRICT Option indicates to refuse the operation if there are foreign keys referencing the table to be truncated.

PostgreSQL TRUNCATE instance

We will compare the efficiency of TRUNCATE and DELETE in emptying a table through the following example.

First, let’s create a table named test_truncate:

CREATE TABLE test_truncate (
  v INTEGER
);

Then, use the following sql statement to insert 10 million rows into this table:

INSERT INTO test_truncate (v)
SELECT generate_series(1, 10000000) v;

Here, the generate_series(1, 10000000) function generated a result set containing 10 million rows.

In order to observe the time consumed by each query, please use the following command to turn on the timer:

\timing

Delete all rows from this table with the following DELETE statement:

DELETE FROM test_truncate;

DELETE 10000000
Time: 6566.458 ms (00:06.566)

The above is the return result of the DELETE statement. It shows that deleting 10000000 rows from the table took 6566.458 ms.

After re-inserting 10 million rows using the above INSERT statement, empty the table with the following TRUNCATE TABLE statement:

TRUNCATE TABLE test_truncate;

TRUNCATE TABLE
Time: 31.785 ms

The above is the return result of the TRUNCATE TABLE statement. It shows that it took 31.785 ms to empty this table. So TRUNCATE is much faster than DELETE .

Conclusion

In this article, we described how to use the TRUNCATE TABLE statement . TRUNCATE TABLE is very useful for emptying a large table.