PostgreSQL TRUNCATE TABLE

In this section, we are going to understand the working of the PostgreSQL TRUNCATE TABLE command to delete all records from huge tables quickly.

To delete all data from a table, we use the DELETE command. When a table contains a lot of records and is also not useful, in that case, we will use the TRUNCATE TABLE command to remove all the records from a particular table.

The Syntax of the truncate command

The basic syntax of the TRUNCATE TABLE command is as below:

In the above syntax, we have the following parameters:

Note: The TRUNCATE TABLE command removes all records from a table without checking it, and it is faster than the DELETE command.

In PostgreSQL, we can perform the truncate table in two ways:

• PostgreSQL Truncate table using pgAdmin
• PostgreSQL Truncate table using SQL Shell (PSQL

PostgreSQL Truncate table using pgAdmin

To delete a table using the truncate table command in pgAdmin, we will follow the following steps:

Step 1

Open your pgAdmin and then go the object tree where we will go to the database, move to the public section under schemas, and select the particular table (student_info) we want to truncate.

Database→ w3cschoool→Schema→ public→ table→ student_info

Step 2

Then we will right-click on the selected (student_info) table, and click on the Truncate option, and then select Truncate option again from the given drop-down list as we can see in the below screenshot:

Step 3

The truncate table popup window will appear on the screen, where we will click on the OK button to truncate the student_info table.

Step 4

Once we clicked on the OK button, the table will be deleted automatically, as we can see in the below screenshot that there is no table available in the Table section.

PostgreSQL Truncate table using SQL Shell (PSQL)

To delete a table using the truncate table command in psql, we will follow the following steps:

Step 1

Open the SQL shell (psql), which appeared with the necessary details. After that, we will log in to the Postgres database server using the password we created during the installation process of PostgreSQL.

And we are connected to the Postgres server as we can see in the below screenshot:

Step 2

Now, we will connect to the specific database server we created earlier that is w3cschoool and with the help of below command:

Step 3

Finally, we will execute the truncate table command to remove the particular table.

Example of PostgreSQL Truncate table in psql

To understand the working of PostgreSQL Truncate table in real-time, we will see the below examples:

Delete all data from one table

In the below example, to remove all data from the student_information table, we use the TRUNCATE TABLE command:

Output

After executing the above command, we will get below output, which says that the student_information table data has been successfully removed.

The below command is used to reset the values in the particular column with the help of the RESTART IDENTITY option:

Let us see one sample example, where we try to reset the values of a particular column.

The below command deletes all rows from the student_information table and returns the order connected with the stu_name column:

Output

After executing the above command, we will get below output which says that the student_information table records has been successfully removed

Essentially the above command does not restart the value in order related to the column in the student_information table because the Truncate table command uses the CONTINUE IDENTITY option by default.

Delete all the records from several tables

If we want to truncate all the records from one or more tables simultaneously, we separate each table with the help of comma (,).

Syntax

The below syntax is used to Delete all data from multiple tables:

For example

The below command is used to delete all data from persons and department tables:

Output

After executing the above command, we will get below output, which displays that the persons and department tables data has been removed successfully.

Remove all records from a table which has foreign key references

If we need to truncate a table frequently which contains the foreign key references of other tables, and that is not listed in the TRUNCATE TABLE command.

By default, the TRUNCATE TABLE command will not delete any records from the table with foreign key references.

Therefore, in that case, we will use the CASCADE option in the TRUNCATE TABLE command for deleting the records from a table and other tables, which contains the foreign key reference.

Syntax

The basic syntax for deleting all records from a table which has foreign key references is as below:

For example

In the below example, we will remove data from the employee table and other tables that reference the persons table through foreign key constraints:

Output

After executing the above command, we will get below output, where the employee table data has been removed successfully.

The CASCADE option is used with an added thought, or if we want to remove records from tables that we did not want.

The TRUNCATE TABLE command uses the RESTRICT option that avoids us from truncating the table, which has foreign key constraint references by default.

PostgreSQL TRUNCATE TABLE and transaction

if we used the truncate table command within a transaction, we could roll it back securely because it is transaction-safe.

PostgreSQL TRUNCATE TABLE and ON DELETE trigger

If we apply the trigger to the TRUNCATE TABLE command used for a table, we must specify the Before Truncate /After Truncate triggers for that particular table.

Whereas the TRUNCATE TABLE command deletes all records from a table, and it does not use any on deleting triggers option to link with the table.

Overview

• To remove all records from a big table, we can use the TRUNCATE TABLE command.
• If a table is referencing the other table through foreign key constraints, we can use the CASCADE option to truncate a table and other tables.
• Instead of using On Delete trigger, the truncate table command uses the Before / After truncate triggers.