How to Use the Truncate Table Command in PostgreSQL

PostgreSQL offers a couple of ways to truncate a particular table. The TRUNCATE TABLE command is one of the most frequently used ways of truncating a table. The TRUNCATE TABLE command can be executed from the SQL SHELL as well as from pg Admin.

This write-up will illustrate the usage of the TRUNCATE TABLE command with the help of different examples. So, let’s start!

How to Truncate a Table in PostgreSQL?

The basic syntax of the TRUNCATE TABLE command will be as follows:

TRUNCATE TABLE tab_name;

Here, tab_name is a table to be truncated.

Parameters of the TRUNCATE TABLE Command

The “TRUNCATE TABLE” command can accept one of the following parameters:

CONTINUE IDENTITY: It is a default option in the TRUNCATE TABLE command that doesn’t modify or restart the value of orders.

RESTART IDENTITY: Resets the identity column.

CASCADE: It truncates all the tables, including those tables that have foreign-key references to other tables.

RESTRICT: It is a default option in the TRUNCATE TABLE that is used to decline truncation if any other tables have a foreign-key reference of tables.

How to Use the TRUNCATE TABLE Statment From the psql?

Open the SQL SHELL and perform the following steps to truncate a table from the selected database.

Step 1: Establish a Connection With the Database

Select a database and establish a connection with the selected table using the “\c” command:

Step 2: Select a Table

Run the “\dt” command to see the list of tables:

image

Choose a table that you want to truncate. Let’s say we need to truncate the staff_details table.

Step 3: Truncate the Table Using TRUNCATE TABLE Command

Run the “TRUNCATE TABLE” command as shown in the following snippet to truncate the selected table:

TRUNCATE TABLE staff_details;

image

The above snippet proves that the TRUNCATE TABLE command gets executed successfully.

How to Truncate a Table Using GUI?

Follow the below steps properly to TRUNCATE a table using pgAdmin:

Step 1: Select a Database

Firstly, open the pgAdmin and select the desired database from the object tree:

image

We selected the “example” database.

Step 2: Select a Table

Within the selected database, locate the public section under the schemas sections, and select a table that you want to truncate:

image

Suppose we want to truncate the “author_details” table.

Step 3: Truncate the Desired Table

Right-click on the selected table and select the “Truncate” option to truncate/delete the desired table:

image

Step 4: Confirm the Table Truncation

Clicking on the Truncate option will open a confirmation window. Click on the “Yes” button to truncate the selected table:

image

Clicking on the “Yes” button will show a confirmation message at the bottom-right side of your computer screen:

image

The above snippet verifies that the selected table has been truncated successfully.

How to Execute TRUNCATE TABLE Command From pgAdmin?

Right-click on the tables section and select the “Query Tool” option:

image

Clicking on the Query Tool option will open the following window:

image

Type the below-given command to truncate the author_details table:

TRUNCATE TABLE author_details;

image

The output clarifies that the selected table has been truncated successfully.

How to Truncate a Table That Has Foreign Key References?

Let’s execute the following command to truncate the article_details table:

TRUNCATE TABLE article_details;

image

The output shows that an error occurred when we tried to truncate the “article_details” table. The error says you can’t truncate a foreign key-referenced table. To deal with such errors, the CASCADE parameter is used along with the TRUNCATE TABLE command.

Let’s execute the following query to truncate the article_details table:

image

The output verified that the article_details table had been truncated successfully.

Conclusion

PostgreSQL provides several methods for truncating a specific table. The TRUNCATE TABLE command is one of them. PostgreSQL offers multiple parameters that can be used with the TRUNCATE TABLE command to achieve different functions. For example, the CASCADE parameter is used to truncate a table along with its dependent objects completely. This write-up demonstrated the working of the TRUNCATE TABLE using relevant examples.