PostgreSQL – DROP SCHEMA

PostgreSQL also supports the deletion of a schema and its objects using the DROP SCHEMA statement.

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

Let’s analyze the above 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 to 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, PostgreSQL uses RESTRICT.

To execute the DROP SCHEMA statement, you must be the owner of the schema that you want to drop or a superuser. PostgreSQL also allows you to drop multiple schemas at the same time by using a single DROP SCHEMA statement.
Now let’s look into some examples.

Example 1:
This example uses the DROP SCHEMA statement to remove the marketing schema present in our database:

DROP SCHEMA IF EXISTS marketing;

To verify so use the below statement:

SELECT * FROM  pg_catalog.pg_namespace ORDER BY nspname;

Output:

Example 2:
The following example uses the DROP SCHEMA statement to drop multiple schemas gfg and Raju using a single statement:

DROP SCHEMA IF EXISTS gfg, raju;

To verify so use the below statement:

SELECT * FROM  pg_catalog.pg_namespace ORDER BY nspname;

Output: