In PostgreSQL, the DROP command drops/deletes a specific database/table. However, Dropping or deleting a table that doesn't exist in the targeted database will result in an error. To tackle such an error, the IF EXISTS parameter can be used with the DROP command.
Using practical examples, this post will show you the difference between the DROP and DROP IF EXISTS commands. So, let’s begin.
How to Drop a Table in PostgreSQL?
Use the DROP TABLE statement to drop the targeted Postgres table. DROP TABLE will have the following syntax:
DROP TABLE tab_name;
tab_name is a table to be dropped/deleted.
Example #1: How Does DROP Command Work in PostgreSQL?
Follow the below given stepwise instructions to drop a table in PostgreSQL:
Step # 1: Establish a Connection With the Selected Database
Firstly, open the SQL SHELL and specify the database name after the \c command to connect to a database of your choice:
\c example;
As the above snippet depicts, we are successfully connected to the selected database.
Step # 2: Check the Available Tables
Run the “\dt” command to see all the tables available in the “example” database”
\dt;
From the available tables, suppose we want to drop the bank_details table.
Step # 3: Drop the Selected Table
The below snippet explains how the DROP command works in PostgreSQL:
DROP TABLE bank_details;
On successful execution of the DROP TABLE command, you will get the following output:
The output demonstrates that the selected table has been dropped successfully.
Step # 4: Verify the Table’s Deletion
The \dt command followed by the table’s name provides the details about the selected table:
\dt bank_details;
The output proves that the bank_details table has been dropped from the example database. Let’s try to drop the selected table (i.e. bank_details) one more time and see how the DROP TABLE command works in such a situation:
DROP TABLE bank_details;
This time, we encountered an error saying that the desired table doesn’t exist.
Example #2: How Does DROP IF EXISTS Command Work in PostgreSQL?
The IF EXISTS is an option that checks the existence of a table. It can be seen from the above example that an error occurs when we tried to drop a table that doesn't exist. We can avoid such an error using the IF EXISTS option.
Now, it's time to learn the working of the DROP IF EXISTS command.
Step #1: Check the Available Tables
Let’s run the \dt command to get all the relations available in the example database:
\dt;
Let’s drop the “emp_data” table.
Step # 2: Drop the Selected Table
Run the DROP IF EXISTS command to drop the emp_data table:
DROP TABLE IF EXISTS emp_data;
So far so good, the selected table has been dropped successfully. Let’s try to drop it one more time:
DROP TABLE IF EXISTS emp_data;
DROP IF EXISTS retrieves a notice instead of throwing an error.
Conclusion
In PostgreSQL, the DROP command is used to drop/delete a specific database/table. However, Dropping or deleting a table that doesn't exist in the targeted database will result in an error. To tackle such an error, the IF EXISTS parameter is used with the DROP command. The DROP command throws an error if a table to be dropped doesn’t exist, while “DROP IF EXISTS” shows a notice instead of throwing an error. This write-up explained the difference between DROP and “DROP IF EXISTS” with the help of examples.
