How to Drop Schema in Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In the world of database management systems, a schema is defined as a collection of database objects. These objects include tables, data types, functions, and operators. The DROP SCHEMA statement can be used to remove a schema from the database when it is no longer needed. In this article, we’ll take a closer look at how to drop a schema in Postgres and review some examples of the DROP SCHEMA statement.

Prerequisites

Before we begin our discussion about how to drop a schema in Postgres, let’s review some basic prerequisites that are essential for this tutorial:

  • You’ll need to have PostgreSQL installed and running on your computer.
  • It’s best to have some basic knowledge of PostgreSQL in order to follow along with the examples that will be presented in this article.

PostgreSQL DROP SCHEMA Syntax

Shown below is the basic syntax for the DROP SCHEMA statement:

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

Let’s talk about each component of this statement:

  • DROP SCHEMA – This statement is used to remove a schema from a PostgreSQL database cluster.

  • IF EXISTS – This optional clause is used to check if a schema exists. When this clause is included, no error will be returned if the specified schema does not exist.

  • CASCADE – This optional clause ensures that when the specified schema is removed, its objects are automatically removed as well.

  • RESTRICT – This optional clause is used to ensure that a schema is deleted only if it contains no objects.

PostgreSQL DROP SCHEMA Examples

Now that we have a better understanding of how the DROP SCHEMA statement works, let’s look at some examples of how to drop a schema in Postgres.

The following list shows all the schemas that we’ve created:

1
2
3
4
5
6
7
8
9
 table_schema | schema_id |  owner
--------------+-----------+----------
 classes      |     24635 | postgres
 courses      |     24640 | postgres
 emp          |     24622 | postgres
 staff        |     24649 | postgres
 students     |     24650 | postgres
 teachers     |     24634 | postgres
(6 ROWS)

DROP SCHEMA

In our first example, we’ll drop a schema in a simple and straightforward way using the DROP SCHEMA statement. We’ll use the following statement:

1
DROP SCHEMA students;

Now, if we select a list of all our schemas again, it should look like this:

1
2
3
4
5
6
7
8
 table_schema | schema_id |  owner
--------------+-----------+----------
 classes      |     24635 | postgres
 courses      |     24640 | postgres
 emp          |     24622 | postgres
 staff        |     24649 | postgres
 teachers     |     24634 | postgres
(5 ROWS)

Notice that the schema named students has been removed.

DROP SCHEMA IF EXISTS

For our next example, we’ll add the IF EXISTS clause to our DROP SCHEMA statement:

1
DROP SCHEMA IF EXISTS teachers;

Once again, we’ll view our list of schemas:

1
2
3
4
5
6
7
 table_schema | schema_id |  owner
--------------+-----------+----------
 classes      |     24635 | postgres
 courses      |     24640 | postgres
 emp          |     24622 | postgres
 staff        |     24649 | postgres
(4 ROWS)

The schema named teachers has been removed. Now that we know that schema has been deleted, let’s try that DROP SCHEMA IF EXISTS statement again and see what happens:

1
2
3
DROP SCHEMA IF EXISTS teachers;
NOTICE:  schema "teachers" does NOT exist, skipping
DROP SCHEMA

As you can see, no error is returned. Instead, you just receive a message letting you know that the schema doesn’t exist and therefore was not deleted.

Drop Multiple Schema

To drop multiple schemas, you can use a statement like the one shown below:

1
DROP SCHEMA classes, staff;

If we select our list of schemas again, it should look like the following:

1
2
3
4
5
 table_schema | schema_id |  owner
--------------+-----------+----------
 courses      |     24640 | postgres
 emp          |     24622 | postgres
(2 ROWS)

We can see that two schemas have been removed.

Drop Schema Cascade

In PostgreSQL, you can’t drop a schema if it has objects in it. For example, let’s try to drop two schemas named courses and emp that contain objects:

1
2
3
4
5
DROP SCHEMA courses, emp;
ERROR:  cannot DROP desired object(s) because other objects depend ON them
DETAIL:  TABLE emp.employee depends ON schema emp
TABLE courses.course depends ON schema courses
HINT:  USE DROP ... CASCADE TO DROP the dependent objects too.

To drop a schema that has objects in it, just include the CASCADE clause as seen in the following statement:

1
DROP SCHEMA courses,emp CASCADE;

Let’s display our list of schemas one more time:

1
2
3
 table_schema | schema_id | owner
--------------+-----------+-------
(0 ROWS)

We have now deleted all the schemas.

Conclusion

Dropping a PostgreSQL schema is a permanent operation, so it’s important to know how to handle the process correctly. In this article, we explained how to use the DROP SCHEMA statement to drop a schema in Postgres. If you’ve followed along with our examples, you’ll be prepared to delete one or multiple schemas in your own Postgres environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.