Drop Schema Postgres

In this article, we will be discussing the deletion of schema from our database. Database Schemas are a group of objects of databases that hold almost every element that the database should have, like triggers, functions, tables, and predefined procedures. In PostgreSQL, only the owner or superuser may remove a schema. There are several checkpoints that we have to go through when we delete a schema; these checks will be discussed further in this article.

Syntax For DROP SCHEMA in PostgreSQL

In PostgreSQL, we use the following query for dropping a schema:

As you can see, the command starts with the keyword of “ DROP ” used in Postgres for deleting, and then we use the keyword “ SCHEMA ” suggesting deleting a schema. Then the query is followed by the “ IF EXIST ” command, which will check for the schema in the database. After this check, we will write the name of the schema with which we saved it in our database. Then we will move on to another set of checks that are the “ CASCADE ” and “ RESTRICT ”.

The “ CASCADE ” command refers to deleting all the objects that a schema is holding, while the “ RESTRICT “ command stops the user from deleting a schema that has some database objects like tables or views. This command also has user-oriented privileges, which means only the authorized user can access the functionality of this command.

Dropping a Schema in PostgreSQL

As we are familiar with the syntax and use of the “ DROP SCHEMA ” command, we will implement this in the PostgreSQL environment. First, we will create some schemas in our database to delete them later. To build a schema in your database, open the pgAdmin window and type the following command in the query editor:

Execute the above to get this result shown below:

Another way of creating a schema is to locate the schema option of your database and Select the ” Create ” option and then the ” Schema… ” options from the browser menu on the left side of the window by right-clicking on those options, respectively.

By right-clicking on the ” Create ” and then the ” Schema… ” options present on the browser menu on the left side of the window.

We’ll build a table in our database for the schema ” s1 ” after we’ve created the schema. We will utilize the appended query to add a table in our schema:

After this, we add some values to the table as well.

After inserting values in our schema’s table, we will move towards deleting our schema from the database.

As we know that the syntax for drop schema in PostgreSQL has various checks, so we can implement this in several ways. These methods are stated below:

1. The DROP SCHEMA query without “IF EXIST” , “CASCADE”, and “RESTRICT” commands.
2. The DROP SCHEMA query without “CASCADE” and “RESTRICT” commands.
3. The DROP SCHEMA query without “IF EXIST” and “RESTRICT” commands.
4. The DROP SCHEMA query with “IF EXIST” and “RESTRICT” commands.
5. The DROP SCHEMA query with “IF EXIST” and “CASCADE” commands.

The DROP SCHEMA Query Without “IF EXIST”, “CASCADE”, and  “RESTRICT” Commands

In this query, we will write a very simple query to delete a schema without any checks in the PostgreSQL environment. We will try to delete both the schemas “s1” and “s2” with this command and see the result.

The output is attached here.

Now run this query.

The schema “s2” was deleted with this command, but the “s2 ” schema could not be deleted because it had other dependencies attached to it, that is, the table that we added before. So, this command can only delete empty schemas.

The DROP SCHEMA Query Without “CASCADE” and “RESTRICT” Commands

In this query for deleting a schema, we will only be using the “ IF EXIST ” command, which will check beforehand whether the schema exists or not. We will use the following query to perform this task:

The output is attached here.

Now run this query.

As the output indicates, this command is only suitable for an empty schema, such as the ” s2 ” schema, which was successfully deleted, however the ” s1 ” schema, which had a table, was unable to be dropped because we did not specify the compiler for cascading the schemas.

The DROP SCHEMA Query Without “IF EXIST” and “RESTRICT” Commands

In this query, we will be adding the “ Cascade ” keyword to the first query that we used without the “ IF EXIST ” check. We shall refer to the query stated below:

The output is appended below.

Now run the following listed query.

Applying the “CASCADE” term in this query instructs the query to delete the schema regardless of what it contains. Thus, we were able to delete both the schemas, one which was empty and the other which had a table in it.

The DROP SCHEMA Query With “IF EXIST” and “RESTRICT” Commands

When we use the “CASCADE” keyword in our query, we are instructing it to delete the schema regardless of what it contains. We will follow the following query for this method:

The output is attached in the affixed image.

Now run the stated query.

The output suggests that we have been able to delete the empty schema, whereas the schema “ s1 ” which had a table in it could not be deleted.

The DROP SCHEMA Query with “IF EXIST” and “CASCADE” Commands

Now we’ll try to delete both schemas by running the ” DROP SCHEMA ” query with both the ” IF EXIST ” and ” CASCADE ” keywords.

The image is attached that reflects the output.

Now execute the appended query.

Because we used the “ CASCADE ” keyword in this query, we could delete both the schemas successfully. This query with both checks is highly recommended because the “ RESTRICT ” keyword is present by default, and you can delete both empty and non-empty schemas with this query.

Conclusion

In this article, we have learned about the different methods of deleting a schema from our database in the PostgreSQL environment. First, we elaborated the concept of schemas and their use in Postgres in brief detail, then, later on, we moved towards its implementation. The syntax of the “ DROP SCHEMA “ command was also discussed in this article. Then the different methods to delete a schema were elaborated and implemented in the PostgreSQL environment.