Introduction to PostgreSQL DROP TABLE statement

To drop a table from the database, you use theDROP TABLE statement as follows:

DROP TABLE [IFEXISTS] table_name[CASCADE| RESTRICT];

In this syntax:

• First, specify the name of the table that you want to drop after theDROP TABLE keywords.
• Second, use theIF EXISTS option to remove the table only if it exists.

If you remove a table that does not exist, PostgreSQL issues an error. To avoid the error, you can use theIF EXISTS option.

If the table is used in other database objects such asviews,triggers, functions, and stored procedures, you cannot remove it. In this case, you have two options:

• Use theCASCADE option to remove the table and its dependent objects.
• Use theRESTRICT option rejects the removal if there is any object depending on the table. TheRESTRICT option is the default if you don’t explicitly specify it in theDROP TABLE statement.

To remove multiple tables simultaneously, you can place the tables separated by commas after theDROP 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 to drop tables.

PostgreSQL DROP TABLE examples

Let’s take some examples of using the PostgreSQLDROP TABLE statement.

1) Drop a table that does not exist

The following statement removes a table namedauthor in the database:

DROP TABLE author;

PostgreSQL issues an error because theauthor table does not exist.

[Err] ERROR:table "author" doesnot exist

To avoid the error, you can use theIF EXISTS option like this.

DROP TABLE IF EXISTS author;

NOTICE:table "author" doesnot exist, skippingDROP TABLE

The output indicates that PostgreSQL issued a notice instead of an error.

2) Drop a table that has dependent objects

The followingcreates new tables calledauthors andpages. Thepages table has a foreign key that references theauthors table.

CREATE TABLE authors (  author_id INT PRIMARY KEY,  firstname VARCHAR (50) NOT NULL,  lastname VARCHAR (50) NOT NULL);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 theDROP TABLE to drop theauthors table:

DROP TABLE IF EXISTS authors;

Because theauthors table has a dependent object which is a foreign key that references thepages table, PostgreSQL issues an error message:

ERROR:  cannotdrop table authors because other objects dependon itDETAIL:constraint pages_author_id_fkeyon table pages dependson table authorsHINT:Use DROP ... CASCADEto drop thedependent objects too.

In this case, you need to remove all dependent objects first before dropping theauthor table or useCASCADE option as follows:

DROP TABLE authors CASCADE;

This statement deletes theauthors table as well as the constraint in thepages table.

If theDROP TABLE statement removes the dependent objects of the table that are being dropped, it will issue a notice like this:

NOTICE:drop cascadesto constraint pages_author_id_fkeyon table pagesDROP TABLE

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 singleDROP TABLE statement to drop thetvshows andanimes tables:

DROP TABLE tvshows, animes;

Summary

• Use theDROP TABLE statement to drop one or more tables.
• Use theCASCADE option to drop a table and all of its dependent objects.

Last updated onJanuary 24, 2024

On this page

• Introduction to PostgreSQL DROP TABLE statement
• PostgreSQL DROP TABLE examples
• Summary