PostgreSQL Tutorial: DROP SCHEMA

Summary: In this tutorial, you will learn how to use the PostgreSQL DROP SCHEMA statement to delete a schema and its objects.

Table of Contents

PostgreSQL DROP SCHEMA statement overview

The DROP SCHEMA removes a schema and all of its objects from a database. The following illustrates the syntax of the DROP SCHEMA statement:

DROP SCHEMA [IF EXISTS] schema_name 
[ CASCADE | RESTRICT ];

In this syntax:

• First, specify the name of the schema from which you want to remove after the DROP SCHEMA keywords.
• Second, use the IF EXISTS option to conditionally delete schema only if it exists.
• Third, use CASCADE to delete schema and all of its objects, and in turn, all objects that depend on those objects. If you want to delete schema only when it is empty, you can use the RESTRICT option. By default, the DROP SCHEMA uses the RESTRICT option.

To execute the DROP SCHEMA statement, you must be the owner of the schema that you want to drop or a superuser.

PostgreSQL allows you to drop multiple schemas at the same time by using a single DROP SCHEMA statement:

DROP SCHEMA [IF EXISTS] schema_name1 [,schema_name2,...] 
[CASCADE | RESTRICT];

PostgreSQL DROP SCHEMA statement examples

Note that the following examples use schemas created in the CREATE SCHEMA tutorial with some modifications in the ALTER SCHEMA tutorial.

1) Using DROP SCHEMA statement to remove an empty schema example

This example uses the DROP SCHEMA statement to remove the accounting schema:

DROP SCHEMA IF EXISTS accounting;

To refresh the schemas in the list, right-click the Schemas node and select the Refresh menu item:

2) Using DROP SCHEMA statement to drop multiple schemas example

The following example uses the DROP SCHEMA statement to drop multiple schemas finance and marketing using a single statement:

DROP SCHEMA IF EXISTS finance, marketing;

3) Using DROP SCHEMA statement to remove a non-empty schema examples

This statement drops the scm schema:

DROP SCHEMA scm;

Here are the messages:

ERROR:  cannot drop schema scm because other objects depend on it
DETAIL:  table scm.deliveries depends on schema scm
view scm.delivery_due_list depends on schema scm
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

So, if the schema is not empty and you want to remove the schema and its objects, you must use the CASCADE option:

DROP SCHEMA scm CASCADE;

Similarly, you can drop the sales schema and its objects using the following statement:

DROP SCHEMA sales CASCADE;

In this tutorial, you have learned how to use the PostgreSQL DROP SCHEMA statement to drop one or more schemas in a database.