Summary: in this tutorial, you will learn how to use the PostgreSQL DROP TABLE statement to remove existing tables from the database.
PostgreSQL DROP TABLE syntax
To remove existing table from the database, you use the DROP TABLE statement as shown following:
1 | DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT]; |
You specify the table name after the DROP TABLE keyword to remove the table permanently from the database.
If you remove a non-existent table, PostgreSQL issues an error. To avoid this situation, you can use the IF EXISTS parameter followed by the DROP TABLE.
In case the table that you want to remove is used in views, constraints or any other objects, the CASCADE allows you 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.
You can put a list of tables after the DROP TABLE to remove multiple tables at once, each table separated by a comma.
Notice that only superuser, schema owner, and table owner have sufficient privilege to remove the table.
PostgreSQL DROP TABLE examples
The following statement removes a table named authorin the database:
1 | DROP TABLE author; |
PostgreSQL issues an error because the authortable does not exist.
1 | [Err] ERROR: table "author" does not exist |
To avoid this error, you can use the IF EXISTS parameter.
1 | DROP TABLE IF EXISTS author; |
PostgreSQL issues a notice instead of an error.
We create new tables named author and page for the next demonstration:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE author ( author_id INT NOT NULL PRIMARY KEY, firstname VARCHAR (50), lastname VARCHAR (50) ); CREATE TABLE page ( page_id serial PRIMARY KEY, title VARCHAR (255) NOT NULL, CONTENT TEXT, author_id INT NOT NULL, FOREIGN KEY (author_id) REFERENCES author (author_id) ); |
You can remove the authortable by using the following statement:
1 | DROP TABLE IF EXISTS author; |
Because the constraint on the pagetable depends on the authortable, PostgreSQL issues an error message.
1 2 3 | [Err] ERROR: cannot drop table author because other objects depend on it DETAIL: constraint page_author_id_fkey on table page depends on table author HINT: Use DROP ... CASCADE to drop the dependent objects too. |
In this case, you need to remove all dependent objects first before removing the authortable or use CASCADE parameter as follows:
1 | DROP TABLE author CASCADE; |
PostgreSQL removes the authortable as well as the constraint in the pagetable. In addition, it issues a notice:
1 | NOTICE: drop cascades to constraint page_author_id_fkey on table page |
In this tutorial, you have learned how to remove existing table from the database by using PostgreSQL ALTER TABLER statement.