Delete vs Truncate in Postgresql

Introduction

In this article we’ll jump straight to the point in discussing delete vs truncate in Postgres SQL, what the differences are, and when each is used.

Delete vs Truncate

Delete and truncate operations are useful when managing data in both small and large tables. Delete is often used when you want to clear data from small tables. Truncate on the other hand is useful for large tables since it is able to clear all the data at once. When using a truncate statement, the operation is faster because it involves removing all rows in the table without scanning them as does with a delete statement. In additions, truncate statement reclaims the storage right away after removing the rows data, thus it does not require subsequent VACUMM operation to obtain the storage. Delete will require the VACUMM operation to restore the memory. This makes the TRUNCATE TABLE operation more efficient in almost all tables.

TRUNCATE and DELETE operations can be done together with other SQL conditions depending on the target results. In this article, we will discuss the application of the two statement that are used when eliminating data from tables. DELETE statement with example applications.

To perform a successful delete statement, you need to provide the table from which the delete operation is done and the condition used to manage the delete process. The syntax of a normal delete statement is given below:

The WHERE clause in this case is optional but omitting it will result to a removal of all the rows in the specified table. The delete statement return the total number of rows affected. Otherwise, if no row was affected through the condition, it returns zero. When we need to view the value of the affected rows, we use the RETURNING clause as indicated in the examples below.

Initially, we check the data in our delete_test table using the select statement:

Then, we perform a delete on the table using delete statement:

Figure 1: Returns 1 as the total number of rows deleted After running another select statement, the result is as follows:

To delete all rows from the table we use the following statement:

And the results are as follows:

TRUNCATE TABLE Statement.

As mentioned earlier, TRUNCATE TABLE statement is normally used when removing all rows from a specified table. We start by creating a truncate_test table and insert some values in it as shown below.

To remove all data from the able table we use the following statement:

Syntax

Sql statement

The figure below shows the result after truncate operation:

Truncate statement can be used to remove all the data from multiple tables using the following syntax:

In our case, we can use the following syntax to remove data from delete_test and the truncate_test tables all at once.

Conclusion

We hope you now clearly understand the difference between delete vs truncate in SQL. We demonstrated some examples using PostgresSQL so you could see it in action.

If you have any questions please don’t hesitate to reach out to us at Object Rocket where we take the complexity of managing production databases out of your hands.