PostgreSQL Drop Schema

In this chapter, we are going to learn how to drop the PostgreSQL schema with the help of a drop schema command to delete a schema and its related objects if we don't need it anymore.

We can easily drop or delete or remove a PostgreSQL schema with the help of the DROP schema statement. If the Schema is not present, it throws an error; that's why we can use the IF EXISTS option with the DROP Schema command. We should be careful while deleting any schema because we will drop all the data and objects available in the particular Schema.

In PostgreSQL, we can drop the Schema in two different ways:

  • PostgreSQL drop schema using psql
  • PostgreSQL schema using pgAdmin 4

PostgreSQL Drop Schema using psql

In PostgreSQL, the drop schema command is used to delete a schema and its related objects from a database.

Syntax to Drop Schema

The syntax of the DROP SCHEMA command is as follows:

Here, we have different parameters that are used to drop the Schema. Let us see them one by one:

Parameters Description
Drop schema It is a keyword, which is used to delete the schema.
If Exists It removes the schema only if it exists.
Schema_name The schema name is used to specify the name of the schema.
Cascade It is used to remove schema and all the objects that rely on those objects.
Restrict PostgreSQL uses the RESTRICT option by default, and it is used to remove only when the schema is empty.

Note: For executing the DROP SCHEMA command, we must be the owner or a superuser of the schema, which we need to drop.

In PostgreSQL, we can drop the schema as well as the objects if it is not required.

  • In psql, the below command is used to drop the schema:
  • Once we execute the above query, we may encounter the error. The particular error states that we cannot drop the schema jtp because some other objects depend on it.
PostgreSQL Drop Schema
  • Therefore, firstly, we have to delete the dependent objects with the help of below command:

PostgreSQL Drop Schema

Output

To see whether the schema is deleted or not, we will execute the below command:

The above command will display the schemas present in the PostgreSQL, and there are only two schemas ( myschema, public) available, which means that the jtp schema is deleted successfully.

PostgreSQL Drop Schema

Note: The PostgreSQL provides us to drop multiple schemas at the same time with the help of a single DROP SCHEMA command.

For example

We can delete multiple schemas present in the w3cschoool database with the help of below command:

PostgreSQL Drop Schema using pgAdmin4

For this, we are going to follow the below process to drop or delete the schema in pgAdmin4:

Step1

  • Firstly, we will right-click on the myschema, which we created earlier and (the schema we do not require anymore) and select the Delete/ Drop option from the given drop-down list:
PostgreSQL Drop Schema

Step2

  • Once we clicked on the delete\drop options, the drop schema popup will appear on the screen, where we click on the Yes button to delete the schema.
PostgreSQL Drop Schema

Step3

  • Once we have clicked on the Yes button, the following error message popup will appear on the screen, where we will click on OK button as we can see in the below image:
PostgreSQL Drop Schema

Step4

  • To delete the schema, we will first drop the dependent object, and for this, we will right-click on the myschema and select the Drop cascaded option from the given drop-down list.
PostgreSQL Drop Schema

Step5

  • The drop cascade schema popup window will appear on the screen, where we click on the Yes button for deleting the dependent objects.
PostgreSQL Drop Schema
  • The myschema is automatically deleted after we delete the dependent objects.





Contact US

Email:[email protected]

PostgreSQL Drop Schema
10/30