Every developer, database user, etc. comes across a situation where he gets confused about “what’s the difference between DROP, TRUNCATE, and DELETE commands”. If you're experiencing the same issues? Then, nothing to worry about. This post is going to resolve all your ambiguities through practical examples.

Quick Outline

This write-up will present a comparative analysis of Postgres TRUNCATE, DROP, and DELETE commands using the following content:

• How to Use TRUNCATE Command in PostgreSQL
• How to Use DELETE Command in PostgreSQL
• How to DROP a Table in PostgreSQL
• Postgres TRUNCATE VS DROP VS DELETE
• Conclusion

So, let’s begin.

How to Use TRUNCATE Command in PostgreSQL

The TRUNCATE TABLE command is a DDL(acronym of data definition language) operation that removes all the data/records from the targeted table. The TRUNCATE TABLE command only removes the table’s data and preserves the table’s structure.

A foreign key-referenced table can't be truncated by the TRUNCATE TABLE command in Postgres. To achieve this purpose, you must use the CASCADE clause/option with the TRUNCATE TABLE command.

Syntax

The below snippet shows how to truncate a specific table in Postgres:

TRUNCATE TABLE tab_name;

Here the tab_name is a table to be truncated.

Example #1: How to Truncate a Table in PostgreSQL

Suppose we need to truncate a table named “emp_data”, whose content is shown in the below-given screenshot:

SELECT * FROM emp_data;

The emp_data has six records. Let’s use the TRUNCATE table command to truncate the targeted table, i.e., emp_data:

TRUNCATE TABLE emp_data;

The emp_data table has been truncated successfully. Let’s use the SELECT command to verify the table’s truncation:

SELECT * FROM emp_data;

From the output, it is clear that the TRUNCATE TABLE command truncated the table’s data and preserved its structure. For more information about TRUNCATE TABLE, click here.

How to Use DELETE Command in PostgreSQL

The DELETE command is a DML(acronym of Data Manipulation Language) command that deletes single or several existing records from the targeted table. The Postgres WHERE clause can be used with the DELETE command to specify a condition, and based on that condition, the table’s records will be deleted. Skipping the WHERE clause from the DELETE command will delete all the records from the certain table.

Syntax

The below snippet illustrates how to use the DELETE command in Postgres:

DELETE FROM tab_name [WHERE condition/criteria];

Tab_name represents the name of the table that needs to be deleted. WHERE is an optional clause that determines whether the whole table will be deleted or some specific table records will be deleted?

Example: How to Use DELETE Statement in PostgreSQL

Suppose we want to delete a table named “staff_details” that contains the following records:

SELECT * FROM staff_details;

Let’s use the DELETE command to delete the focused table, i.e., the staff_details table:

DELETE FROM staff_details
WHERE staff_id = 4;

The output indicates that the targeted record has been deleted from the “staff_details” table. To verify the deletion of the record, you can utilize the below command:

SELECT * FROM staff_details;

The specified record, i.e., “staff_id=4”, has been deleted from the staff_details table. You can learn more about the DELETE command from this article.

How to DROP a Table in PostgreSQL

The DROP command is a DDL(acronym of Data Definition Language) command that drops/deletes the existing objects of a database permanently. Using the DROP command, you can drop a database, table, trigger, etc. If we talk about the DROP TABLE command, it drops an existing table from a database. It not only drops the table but also deletes the table’s structure permanently.

Syntax

The following snippet demonstrates the usage of the DROP TABLE command in Postgres:

DROP TABLE tab_name;

The tab_name represents a table that will be dropped.

Example: How Does the DROP TABLE Command Work in Postgres?

Let’s say we have to drop the programming_languages table that consists of the following records:

SELECT * FROM programming_languages;

To drop the programming_languages table, we will run the below command:

DROP TABLE programming_languages;

The above snippet states that the programming_languages table has been dropped from the respective database. Let’s verify the table’s deletion using the SELECT command:

SELECT * FROM programming_languages;

The output proved that the DROP TABLE command successfully dropped the structure and data of the programming_languages table.

Postgres TRUNCATE VS DROP VS DELETE

The DROP command drops/removes the table from the database completely, i.e., including the table's structure. While the DELETE and TRUNCATE commands remove entries/records from the given table and retain the table structure. The TRUNCATE command always removes all the data of the selected table, while the DELETE command can delete the entire table or some specific table entries depending upon the specified condition. The DELETE command can be customized using the WHERE clause to remove some selective table records. While the TRUNCATE command can't be used with the WHERE clause.

The TRUNCATE command is faster than the DELETE query and has no match when it comes to deleting the entire table's data.

That was all the necessary information regarding the TRUNCATE, DROP, and DELETE statements.

Conclusion

In Postgres, the TRUNCATE TABLE command only removes the table’s data and preserves the table’s structure. The DELETE command deletes single, several, or all the existing records from the targeted table. While the DROP TABLE command not only drops the table’s data but also deletes the table’s structure permanently. Through relevant examples, this post explained the difference between the TRUNCATE, DROP, and DELETE commands.