PostgreSQL DROP DATABASE: Delete a Database

In this tutorial, we will see how we can delete a database using the PostgreSQL DROP DATABASE command. Moreover, we will also see how to delete a database using pgAdmin4 which is a GUI tool for PostgreSQL. So without further ado, let’s get started!

Also read: PostgreSQL – Alter Database with Examples

Introduction to DROP DATABASE in PostgreSQL

The DROP DATABASE command and its terminologies in PostgreSQL are pretty similar to other database systems. This command removes the database from the server with all the data associated with it including the tables, stored programs, triggers etc.

It is important to be cautious when dropping a database, as this operation cannot be undone.

Now let’s see the syntax, rules and example of the DROP DATABASE command.

PostgreSQL DROP DATABASE Syntax

Below is the syntax to delete the database in PostgreSQL.

DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]

where option can be:

    FORCECode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

• IF EXISTS – The IF EXISTS clause prevents the error message if the database does not exist that you are trying to delete. Otherwise, you will get an error.
• name – Name of the database that you want to delete.
• FORCE – If this option is specified, PostgreSQL will terminate all the connections of the target database. If the user has no permission to terminate other connections, PostgreSQL will throw an error.

You can not drop the database that you are currently connect to.

Also read: PostgreSQL – How to Create a Database

PostgreSQL DROP DATABASE Example

Now let’s see some examples of the DROP DATABASE command.

First, log in to the psql shell using the following command and the password for the user.

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now list all databases on the server using the \l command.

As you can see, there are a bunch of databases available on the server.

Example 1 – DROP DATABASE with IF EXISTS

We will try to delete the database that does not exist.

DROP DATABASE noExistsDatabase;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The above command will give an error saying “database “noexistsdatabase” does not exist”. However, we can skip this error message by simply adding the IF EXISTS clause.

DROP DATABASE IF EXISTS noExistsDatabase;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now we will get a notification message saying the same message as the above.

Example 2 – PostgreSQL DROP DATABASE with FORCE

Now let’s try to delete the database that has already connection established by another instance.

DROP DATABASE journaldev;

If the Journaldev database is being accessed somewhere else, you will get an error saying “database “journaldev” is being accessed by other users” with a detailed message “There are n other sessions using the database.”

Now let’s try to delete this database using the FORCE option.

DROP DATABASE journaldev WITH (FORCE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see in the image above, the first query without the FORCE option raised an error. After removing the database using the FORCE option, it gets completely removed from the server.

Example 3 – Drop Database using pgAdmin4

Using pgAdmin, you can drop the database with a single click. Let’s see how.

Open the pgAdmin and connect to the server. After that, right-click on the database name under the “Databases” option.

You will see the option Delete/Drop. Click on it. You will get a confirmation popup message if you are sure to drop the database. Click on yes.

That’s it. The database will get removed from the server.

Conclusion

In this tutorial, we have seen the DROP DATABASE command to remove the database from the server. Deleting a database in PostgreSQL is a straightforward process that can be accomplished by connecting to the database server, listing the available databases, using the “DROP DATABASE” command, and confirming the operation. However, Exercise caution when dropping a database as the operation is irreversible and cannot be undone.

Reference

Postgresql official documentation on the DROP DATABASE.