PostgreSQL - TRUNCATE TABLE

The PostgreSQL TRUNCATE TABLE statement is used to delete complete data from an existing table. The PostgreSQL DROP TABLE statement can also be used to delete complete data of a table but it will delete whole table structure from the database. Hence, TRUNCATE TABLE statement is useful when a table need to be emptied but the table structure is retained.

Syntax

The syntax of using TRUNCATE TABLE statement in PostgreSQL is given below:

TRUNCATE TABLE table_name;

Example:

Consider a database containing a table called Employee with the following records:

The description of the table can be checked using \d command as shown below:

\d Employee;

This result of the above code will be:

                     Table "public.employee"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 empid  | integer                |           | not null | 
 name   | character varying(255) |           | not null | 
 city   | character varying(100) |           |          | 
 age    | integer                |           |          | 
 salary | numeric(18,2)          |           |          | 
Indexes:
    "employee_pkey" PRIMARY KEY, btree (empid)

To truncate this table, the statement is given below:

TRUNCATE TABLE Employee;

After truncating the table, the \d command will still show the same structure as shown above but the table will contain no records.