How to Drop Schema in Postgres
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