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:
![image](/media/images/image_k6wxex1.width-1200.png)
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.
![image](/media/images/image_qFpAXk0.width-1200.png)
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;
![image](/media/images/image_TKOXVzL.width-1200.png)
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
![image](/media/images/image_TZ958qW.width-1200.png)
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
![image](/media/images/image_SnS3y87.width-1200.png)
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:
![image](/media/images/image_O9Vk8Ii.width-1200.png)
Step 2: drop database
Right click on the desired database and select the “Delete/Drop” option to drop the particular database:
![image](/media/images/image_SkcvGEt.width-1200.png)
Step 3: Confirmation
A confirmation notification will appear once you click on the “Delete/Drop” option:
![image](/media/images/image_Y2Z9qPI.width-1200.png)
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.