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:
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;
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:
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:
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:
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:
Clicking on the “Yes” button will show a confirmation message at the bottom-right side of your computer screen:
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:
Clicking on the Query Tool option will open the following window:
Type the below-given command to truncate the author_details table:
TRUNCATE TABLE author_details;
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;
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:
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.
