PostgreSQL TRUNCATE TABLE

In PostgreSQL, TRUNCATE TABLE is used to delete the data in a table, but not the table structure.

You can also use DROP TABLE to delete a table, but this command will delete the table structure as well. If you want to insert data, you need to recreate the table.

TRUNCATE TABLE has the same effect as DELETE, but since it does not actually scan the table, it is faster. In addition, TRUNCATE TABLE can immediately free up table space without requiring a subsequent VACUUM operation, which is very useful for large tables.

The PostgreSQL VACUUM operation is used to free up and reuse disk space occupied by updated/deleted rows.

Syntax

The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE  table_name;

Examples

Create the COMPANY table (Download COMPANY SQL file), the data content is as follows:

exampledb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example uses TRUNCATE TABLE to clear the COMPANY table:

exampledb=# TRUNCATE TABLE COMPANY;

The result obtained is as follows:

exampledb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Other Extensions