PostgreSQL DROP SCHEMA Statement

Summary: In this tutorial, you’ll learn how to remove a schema from a database using the PostgreSQL DROP SCHEMA statement.

PostgreSQL DROP SCHEMA Statement Overview #

The DROP SCHEMA statement removes one or more schemas from a database.

Here’s the syntax of the DROP SCHEMA statement:

DROP SCHEMA [IF EXISTS] schema_name
[CASCADE | RESTRICT];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the schema you want to remove.
• Second, use the IF EXISTS to prevent an error from removing a schema that does not exist.
• Third, use the CASCADE option to remove the objects that belong to the schema, such as tables, views, and indexes, and in turn, all objects that depend on those objects. By default, the statement uses the RESTRICT option that rejects to drop the schema if it contains any objects.

Note that the CASCADE option may drop the objects that belong to other schemas.

If you want to drop multiple schemas at once, you can list them out in the DROP SCHEMA statement:

DROP SCHEMA schema1, schema2, schema3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Only the owner of a schema or superusers can drop the schema.

Dropping one schema and its objects #

First, create a new schema called payrolls:

CREATE SCHEMA payrolls
    CREATE TABLE pay_grades (
          id INT PRIMARY KEY,
          grade VARCHAR(20) NOT NULL,
          min_salary DEC NOT NULL,
          max_salary DEC NOT NULL
    );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, drop the schema:

DROP SCHEMA payrolls;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued an error:

ERROR:  cannot drop schema payrolls because other objects depend on it
DETAIL:  table payrolls.pay_grades depends on schema payrolls
HINT:  Use DROP ... CASCADE to drop the dependent objects too.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The reason is that the payrolls schema contains a table pay_grades.

Third, drop the payrolls schema with the CASCADE option:

DROP SCHEMA payrolls CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  drop cascades to table payrolls.pay_grades
DROP SCHEMACode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement drops the payrolls schema as well as the payrolls.pay_grades table.

Dropping multiple schemas #

First, create two new schemas called procurements and mrp:

CREATE SCHEMA procurements;
CREATE SCHEMA mrp;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, drop two schemas at the same time using the DROP SCHEMA statement:

DROP SCHEMA procurements, mrp;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the DROP SCHEMA statement to drop one or more schemas along with their objects.