When a PostgreSQL database is no longer needed, we can delete/drop it using SQL SHELL, command prompt, and pgAdmin. To do that, PostgreSQL provides a couple of commands/statements such as DROP DATABASE and dropdb. So, let’s learn how to drop/delete a specific database in PostgreSQL.

How to drop/delete a PostgreSQL database using SQL SHELL?

The DROP DATABASE command can be executed from the SQL SHELL to drop/delete a PostgreSQL database. The DROP DATABASE statement deletes the catalogs and directory associated with that database. Here is the basic syntax of the DROP DATABASE statement:

DROP DATABASE [IF EXISTS] dbname;

Let’s understand the above-given command step-by-step:

- DROP DATABASE is a command to delete a specific PostgreSQL database.

- If the specified database does not exist, the IF EXISTS parameter will issue a notice rather than throwing an error.

- dbname is the database to be deleted.

Let’s execute the “\l” command to check the list of all the available databases:

Let’s assume we need to drop a database named “example” using psql. To do so, we will execute the following command:

DROP DATABASE IF EXISTS example;

The above command will serve the following functionalities:

- testdb is a database name.

- IF EXISTS will check the existence of the example.

- DROP DATABASE will delete the given database, i.e. example.

The above snippet verified that the database named “example” has been deleted successfully.

Let’s execute the DROP DATABASE command one more time to remove the same database i.e. example:

DROP DATABASE IF EXISTS example;

This time the DROP DATABASE command issued a notice that the “example” database doesn’t exist.

How to drop/delete a PostgreSQL database using Command Prompt?

In Postgres, the dropdb command can be executed from the command prompt(cmd) to delete any specific database. The dropdb command will have the following syntax:

dropdb [parameter/option] dbname

Here is the detailed explanation of the above snippet:

- dropdb is a command that drops/deletes a database.

- parameters/options are the command-line arguments that the dropdb command can accept.

- dbname is the database name to be deleted.

Below is a list of parameters that dropdb command can accept:

–help

It is used to get help regarding the dropdb command.

-if exists

It issues a notice rather than throwing an error if the given database doesn’t exist.

-e

It is used to display the commands to be sent to the server

-i

It shows a verification prompt before deletion.

-V

It shows the dropdb’s version.

-h

It is used to specify the host name for the server’s machine.

-U

It is used to specify the user name to connect with.

-p

It is used to specify the port on which the server listens for the connections.

maintenance db-=dbname

It takes the name of the connected database to remove that specific database.

-w

Use -w option if you don’t want to prompt a password for the dropdb.

-W

It is used to prompt a password for the dropdb command.

Let’s follow the below steps to delete a database named “exampledb” using the dropdb command:

Step 1: Access Postgres

Firstly, access the bin directory of the PostgreSQL by using the cd command followed by the complete path:

C:\Program Files\PostgreSQL\14\bin

Step 2: Execute dropdb command

Let’s execute the dropdb command from the command prompt to remove a database named “exampledb”:

dropdb -U postgres exampledb

When you execute the above mentioned command, it will ask you to enter a password. Once you provide the appropriate password then the dropdb command will delete the database named “exampledb” connected to the user named postgres.

How to drop/delete a PostgreSQL database using pgAdmin?

You can drop a PostgreSQL database using a GUI i.e. pgAdmin. Follow the below-given steps to learn how to drop a Postgres database using pgAdmin:

Step 1: select database

Open the pgAdmin and pick the database you want to drop:

Step 2: drop database

Right click on the desired database and select the “Delete/Drop” option to drop the particular database:

Step 3: Confirmation

A confirmation notification will appear once you click on the “Delete/Drop” option:

Clicking on the “Yes” button will drop the selected database.

Conclusion

In PostgreSQL, DROP DATABASE and dropdb commands are used to delete a specific database. You can execute the DROP DATABASE command from psql while dropdb command from the command prompt to remove/drop a database. Moreover, a PostgreSQL database can be deleted using pgAdmin. This write-up explained the different ways to drop a PostgreSQL database.