PostgreSQL TRUNCATE TABLE

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

You can also use DROP TABLE to delete the table, but this command will delete the table structure along with the table. If you want to insert data, you need to rebuild this table.

TRUNCATE TABLE has the same effect as DELETE, but it is faster because it actually does not scan the table. In addition, TRUNCATE TABLE can immediately release the table space without the need for subsequent VACUUM operations, which is very useful for large tables.

The PostgreSQL VACUUM operation is used to release and reuse updated/Delete the disk space occupied by the rows.

Syntax

The basic syntax of TRUNCATE TABLE is as follows：

TRUNCATE TABLE　table_name;

Online example

Create COMPANY table（Download COMPANY SQL file ），Data content as follows：

w3codeboxdb# 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:

w3codeboxdb=# TRUNCATE TABLE COMPANY;

The results are as follows:

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