In PostgreSQL, a schema is a namespace that offers a variety of objects, such as indexes, tables, views, functions, datatypes, operators, and sequences. In Postgres, users can create a schema, drop a schema, and alter a schema. A schema can be dropped in Postgres by using the DROP SCHEMA statement. When you drop a schema, all the objects present in it will also be dropped.
The purpose of this guide is to demonstrate the usage of the “DROP SCHEMA” statement in PostgreSQL:
- Method 1: Using SQL Shell Drop a Schema in PostgreSQL
- Method 2: Using pgAdmin Drop a Schema in PostgreSQL
- Method 3: DROP SCHEMA Statement to Drop a Schema Via pgAdmin
Let's discuss the first method.
Method 1: Using SQL Shell Drop a Schema in PostgreSQL
The below snippet illustrates the syntax of the DROP SCHEMA statement:
DROP SCHEMA schema_name;
In the above syntax, the “schema_name” specifies the name of the existing schema in the database.
Let’s utilize the “\dn” command to display the list of existing schemas in the selected database:
To drop an existing schema, execute the “DROP SCHEMA” statement by specifying the schema's name such as “emp_data”:
DROP SCHEMA emp_data;
The command mentioned above will drop the “emp_data” schema from the “postgres” database:
The output clarifies that the selected schema, i.e., “emp_data”, has been drooped successfully.
Method 2: Using pgAdmin Drop a Schema in PostgreSQL
To drop a schema via pgAdmin, firstly, you have to right-click on the targeted schema; consequently, a pop-up menu will appear, select the “Delete/Drop” option:
It navigates you to a new pop-up window stating:
Select the “Yes” button to drop the targeted schema.
Method 3: DROP SCHEMA Statement to Drop a Schema Via pgAdmin
The pgAdmin provides the “Query Tool” to write and execute statements in the GUI environment. For instance, the “DROP SCHEMA” statement can be executed via pgAdmin for deleting an existing schema.
To access the “Query Tool”, press the right-click on the existing schema’s name, such as “std_info”, and click on the query tool option:
Once the query tool is opened, you can execute any statement of your choice to achieve the desired functionality:
DROP SCHEMA std_info;
In the above statement, “std_info” is a schema to be dropped:
The “DROP SCHEMA” message in the output shows that “std_info” has been dropped successfully. Now, right-click on the targeted database, i.e., “std_info”, and hit the “Refresh” button to refresh/reload the database objects:
The output shows that the std_info schema has been dropped from the db_store database.
Great Work! You have experienced the “DROP SCHEMA” statement in PostgreSQL.
Use the DROP SCHEMA statement to drop/remove a specific schema from a database. In PostgreSQL, a schema is a collection of several objects, so when you drop a schema, all the objects present in it will also be dropped. This write-up experienced several examples to explain all the essential methods of dropping a schema in PostgreSQL.