PostgreSQL has a DROP TABLE statement that is used to remove an existing table or tables from the database.
Syntax: DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
Let’s analyze the above syntax:
- We specify the table name after the DROP TABLE keyword to remove the table permanently from the database.
- In case the table that you want to remove is used in views, constraints, or any other objects, the CASCADE allows users to remove those dependent objects together with the table automatically.
- RESTRICTrefuses to drop table if there is any object depends on it. PostgreSQL uses RESTRICT by default.
- We can put a list of tables after the DROP TABLE to remove multiple tables at once, each table separated by a comma.
- We can put a list of tables after the DROP TABLE to remove multiple tables at once, each table separated by a comma.
- It is important to note that only superuser, schema owner, and table owner have sufficient privilege to remove the table.
Example 1:
We will remove the author table from our database using the below statement:
DROP TABLE author;
PostgreSQL issues an error because the author table does not exist.
To avoid this error, you can use the IF EXISTS parameter as follows:
DROP TABLE IF EXISTS author;
This will lead to PostgreSQL issuing a notice instead of an error.
Output:
Example 2:
Here we will remove a table that already exists in our database. To check for all available tables in the database use the below command:
\dt
It will list all existing table as below:
Here we will remove the categories table using the below statement:
DROP TABLE categories;
Now check the list of tables again to verify the removal using the below command:
\dt
Output: