How to Drop if Exists vs Drop in PostgreSQL
The DROP
statement deletes the table from the database. But only the DROP
statement shows an error if the table we’re trying to delete doesn’t exist.
Using the DROP IF EXISTS
, we can eliminate the error.
DROP
vs DROP IF EXISTS
in PostgreSQL
Let’s try to drop a table that doesn’t exist in the database.
Example:
postgres=# drop table account;
ERROR: table "account" does not exist
postgres=#
postgres=# drop table if exists account;
NOTICE: table "account" does not exist, skipping
DROP TABLE
postgres=#
When we use drop
, it shows an error stating that the table doesn’t exist. But, using drop table if exists
shows a notice that the table doesn’t exist; it is not throwing any error.
Drop a Table With Other Tables Dependent On
Let’s say we have two tables, one for sale and another for the customer record. The Sales
table records the customers and their purchases, so deleting the Customer
table must be done with the CASCADE
option.
Let’s create two tables first.
CREATE TABLE Customer(
id INT PRIMARY KEY,
full_name VARCHAR,
branch VARCHAR
);
CREATE TABLE Sales(
id INT PRIMARY KEY,
item_name VARCHAR,
price DECIMAL,
customer_id int,
FOREIGN KEY (customer_id) REFERENCES Customer(id)
);
Now, let’s try to delete the Customer
table:
postgres=# drop table customer;
ERROR: cannot drop table customer because other objects depend on it
DETAIL: constraint sales_customer_id_fkey on table sales depends on table customer
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=#
As we’ve discussed, the Sales
depends on the Customer
table, which shows this error.
Try the CASCADE
option with the DROP
statement. Here’s how we can do it.
postgres=# DROP TABLE IF EXISTS Customer CASCADE;
NOTICE: drop cascades to constraint sales_customer_id_fkey on table sales
DROP TABLE
postgres=#
Now, the table Customer
is deleted.
Here, we’re checking if the Customer
table exists or not. If it exists, then we are deleting it with CASCADE
.
To know more about dropping a table, visit the following official documentation.