In this tutorial, we will be learning about the DROP TABLE statement in PostgreSQL which is used to remove the existing table from the database. Apart from just deleting the table, we will learn some additional things as well. So, let’s get started!
PostgreSQL DROP TABLE Statement
The DROP TABLE is a DDL command which is available in nearly all database systems and is used to remove the table from the database with all the records in it.
To drop the table from the database, you must be a table owner, schema owner or super user of that table. Note that, the DROP TABLE completely removes the table from the database with all the data in it. Moreover, you can also drop the tables which are referencing another table using foreign-key relationships.
Now let’s see the syntax of the DROP TABLE statement.
PostgreSQL DROP TABLE Syntax
Following is the syntax of using the DROP TABLE statement in PostgreSQL.
Syntax:
DROP TABLE [ IF EXISTS ] tableName [, ...] [ CASCADE | RESTRICT ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here,
- DROP TABLE – The important keyword to proceed with. Note that, the TABLE keyword is mandatory, unlike the TRUNCATE TABLE syntax.
- IF EXISTS – It prevents the error if the mentioned table doesn’t exist.
- tableName – One or more table names separated by a comma.
When there are other objects dependent on the mentioned table such as triggers, views, functions, stored procedures or referencing tables, you can not delete that table. In this case, you have two choices, either you can delete all the child objects or restrict the deletion of all objects including mentioned table. Let’s see those two options.
- CASCADE – The CASCADE keyword will delete the mentioned table as well as all the referencing objects down below the mentioned table. Note that, the CASCADE will drop only views and triggers. However, in the foreign-key case, it will only remove the foreign-key constraint from the child table and not the entire table.
- RESTRICT – The RESTRICT keyword will prevent the deletion of the mentioned table as well as all the child objects. PostgreSQL uses the RESTRICT keyword by default.
Now that we are clear with the syntax of the DROP TABLE statement, let’s proceed to see some examples of it.
PostgreSQL DROP TABLE Examples
Before proceeding, we need a table. Here, we will create two tables, the first table will be referenced by the second table by the foreign-key relationship.
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY,
author_name VARCHAR(100) NOT NULL
);
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
book_title VARCHAR(200) NOT NULL,
author_id INT REFERENCES authors(author_id)
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the table has been created successfully. Let’s now head towards some examples.
Drop Non-Existing Table
If you try to drop the table that doesn’t exist, PostgreSQL will throw an error. Here, we will mention the table name as ‘author’ which doesn’t exists in our database.
DROP TABLE author;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)The above query throws an error that table doesn’t exist. In this case, we can use the IF EXISTS keyword with the DROP TABLE statement which will show us a notice instead of throwing an error.
DROP TABLE IF EXISTS author;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the first query returns an error, whereas the second query returns the notice.
DROP TABLE With Foreign-key Reference
We created two tables above- ‘authors’ and ‘books’. The ‘books’ table has a foreign-key relationship with the ‘authors’ table.
Let’s try to delete the parent table, i.e., the ‘authors’ table.
DROP TABLE IF EXISTS authors;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see here, PostgreSQL has thrown an error that the table can not be dropped because other objects depend on it. Along with the error, it has given us a hint that if you want to delete the dependent objects, use the CASCADE keyword.
So, let’s use the CASCADE keyword to delete the parent table as well as the child table.
DROP TABLE IF EXISTS authors CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the ‘authors’ table will be removed from the database. However, the ‘books’ table which is the child will not be removed. Only the foreign-key constraint from the ‘books’ table will be removed.
Let’s see the output of the above query.
As you can see, the ‘authors’ table has been dropped successfully. Let’s see the table description of the ‘books’ table and check if the foreign-key constraint is also removed.
As you can see, there is no foreign-key constraint available on the table.
Drop Multiple Tables
To drop multiple tables, you just have to mention multiple table names separated by a comma. Let’s see.
Here, we have removed the foreign-key constraint of the ‘authors’ table and the ‘books’ table and created them in the database.
DROP TABLE authors, books;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, both tables have been deleted successfully.
Conclusion
In this tutorial, we have learned about the DROP TABLE statement in PostgreSQL which is used to remove the table from the database. Although it is a very simple yet powerful tool, you must be very precocious as this action is irreversible and permanent. I hope you learned some new concepts like CASCADE and RESTRICT that you might have missed.