How to Drop if Exists vs Drop in PostgreSQL

1. DROP vs DROP IF EXISTS in PostgreSQL
2. Drop a Table With Other Tables Dependent On

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.