August 2, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL DROP TABLE statement to remove existing tables from the database.
Introduction to PostgreSQL DROP TABLE statement
To drop a table from the database, you use the
DROP TABLE statement as follows:
DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
In this syntax:
- First, specify the name of the table that you want to drop after the
- Second, use the
IF EXISTSoption to remove the table only if it exists.
If you remove a table that does not exist, PostgreSQL issues an error. To avoid this situation, you can use the
IF EXISTS option.
In case the table that you want to remove is used in other objects such as views, triggers, functions, and stored procedures, the
DROP TABLE cannot remove the table. In this case, you have two options:
CASCADEoption allows you to remove the table and its dependent objects.
RESTRICToption rejects the removal if there is any object depends on the table. The
RESTRICToption is the default if you don’t explicitly specify it in the
To remove multiple tables at once, you can place a comma-separated list of tables after the
DROP TABLE keywords:
DROP TABLE [IF EXISTS] table_name_1, table_name_2, ... [CASCADE | RESTRICT];
Note that you need to have the roles of the superuser, schema owner, or table owner in order to drop tables.
PostgreSQL DROP TABLE examples
Let’s take some examples of using the PostgreSQL DROP TABLE statement
1) Drop a table that does not exist
The following statement removes a table named
authorin the database:
DROP TABLE author;
PostgreSQL issues an error because the
author table does not exist.
ERROR: table "author" does not exist
To avoid the error, you can use the
IF EXISTS option like this.
DROP TABLE IF EXISTS author;
NOTICE: table "author" does not exist, skipping DROP TABLE
As can be seen clearly from the output, PostgreSQL issued a notice instead of an error.
2) Drop a table that has dependent objects
The following creates new tables called
CREATE TABLE authors ( author_id INT PRIMARY KEY, firstname VARCHAR (50), lastname VARCHAR (50) ); CREATE TABLE pages ( page_id serial PRIMARY KEY, title VARCHAR (255) NOT NULL, contents TEXT, author_id INT NOT NULL, FOREIGN KEY (author_id) REFERENCES authors (author_id) );
The following statement uses the
DROP TABLE to drop the
DROP TABLE IF EXISTS authors;
Because the constraint on the
page table depends on the
authortable, PostgreSQL issues an error message:
ERROR: cannot drop table authors because other objects depend on it DETAIL: constraint pages_author_id_fkey on table pages depends on table authors HINT: Use DROP ... CASCADE to drop the dependent objects too. SQL state: 2BP01
In this case, you need to remove all dependent objects first before dropping the
author table or use
CASCADE option as follows:
DROP TABLE authors CASCADE;
PostgreSQL removes the
authortable as well as the constraint in the
DROP TABLE statement removes the dependent objects of the table that is being dropped, it will issue a notice like this:
NOTICE: drop cascades to constraint pages_author_id_fkey on table pages
3) Drop multiple tables
The following statements create two tables for the demo purposes:
CREATE TABLE tvshows( tvshow_id INT GENERATED ALWAYS AS IDENTITY, title VARCHAR, release_year SMALLINT, PRIMARY KEY(tvshow_id) ); CREATE TABLE animes( anime_id INT GENERATED ALWAYS AS IDENTITY, title VARCHAR, release_year SMALLINT, PRIMARY KEY(anime_id) );
The following example uses a single
DROP TABLE statement to drop the
DROP TABLE tvshows, animes;
- Use the
DROP TABLEstatement to drop a table.
- Use the
CASCADEoption to drop a table and all of it dependent objects.