PostgreSQL: DROP TABLE Statement

In this PostgreSQL post explains how to use the PostgreSQL DROP TABLE statement with syntax and examples.

Description

The PostgreSQL DROP TABLE statement allows you to remove or delete a table from the PostgreSQL database.

Syntax

In its simplest form, the syntax for the DROP TABLE statement in PostgreSQL is:

DROP TABLE table_name;

However, the full syntax for the PostgreSQL DROP TABLE statement is:

DROP [ TEMPORARY ] TABLE [ IF EXISTS ]
table_name1, table_name2, ...
[ RESTRICT | CASCADE ];

Parameters or Arguments

TEMPORARY

Optional. It specifies that only temporary tables should be dropped by the DROP TABLE statement.

table_name

The name of the table to remove from the database.

table_name1, table_name2

The tables to remove from the database, if removing more than one table in the DROP TABLE statement.

IF EXISTS

Optional. If specified, the DROP TABLE statement will not raise an error if one of the tables does not exist.

RESTRICT

Optional. If specified, the table will only be dropped if there are no other objects that depend on it.

CASCADE

Optional. If specified, the table will be dropped and any objects that depend on it will be automatically dropped.

Note

• If you use the DROP TABLE statement in PostgreSQL to drop one or more tables that do not exist, the database will raise an error (unless you specify the IF EXISTS parameter in the DROP TABLE statement).

Example

Let's look at an example that shows how to drop a table using the PostgreSQL DROP TABLE statement.

Drop One Table

First, let's look at a simple DROP TABLE example that shows how to use the DROP TABLE statement to drop one table in PostgreSQL.

For example:

DROP TABLE order_details;

This DROP TABLE example would delete the table called order_details.

Drop Multiple Tables

Let's look at an example where we want to drop more than one table using the DROP TABLE statement:

For example:

DROP TABLE order_details, products;

This DROP TABLE statement example would delete two tables - order_details and products. If we were worried that one of the tables doesn't exist and we don't want to raise an error, we could modify our DROP TABLE statement as follows:

DROP TABLE IF EXISTS order_details, products;

This example would delete the order_details and products tables and would not raise an error if one of the tables didn't exist.