Introduction
PostgreSQL offers two methods to drop a database: the DROP DATABASE statement or the dropdb shell utility.
Removing unused databases is good practice and helps keep the workspace clean. However, deleting an existing PostgreSQL database removes all catalog entries and data for that database.
Continue reading to learn how to drop a database in PostgreSQL.
Prerequisites
- PostgreSQL 10+ (see how to check the PostgreSQL version).
- Access to the terminal with sudo privileges.
DROP DATABASE Statement Syntax
The syntax for the statement is:
DROP DATABASE [database_name];The statement removes the directory containing the database information and the catalog entries. If a database is in use or non-existent, the command does not execute.
Important: Only the database owner can delete a database.
Drop an Existing Database
To drop an existing database:
sudo -i -u postgres psql
The terminal changes to the Postgres shell.
2. List all the databases with:
\l
The output shows a table listing all databases.
3. To drop the database in PostgreSQL, locate the database name from the list and provide it in the following command:
DROP DATABASE [database_name];
The dropped database no longer appears in the listing.
Drop a Non-Existing Database
If a database doesn't exist, the DROP DATABASE statement shows an error message:
Add the IF EXISTS option to check if a database exists before deleting it. If the database exists, the statement drops the database. Otherwise, it shows an informative notice. The syntax is:
DROP DATABASE IF EXISTS [database_name];
Use this method to perform a check and avoid code errors.
Drop a Database With Active Connections
The DROP DATABASE method does not remove the database if it is in use. Instead, the terminal prints an error message stating that a database session is open:
Add the WITH (FORCE) to close the session and delete the database forcefully:
DROP DATABASE [database_name] WITH (FORCE);
If possible, Postgres closes the user's session and deletes the database forcefully.
Note: The WITH (FORCE) option is available in PostgreSQL version 13 and higher.
dropdb Utility
The dropdb shell utility is a wrapper for the DROP DATABASE command. Although the utility is identical in effect, dropdb allows the removal of databases remotely. It is the preferred method when performing commands on a remote database server.
The syntax is:
dropdb [connection_parameters] [options] [database_name]For example, the -i option runs the command in interactive mode, and -e prints the query command:
dropdb -i -e example
Because of the -i tag, the program asks for confirmation before deleting the database. Press y to confirm the deletion or n to cancel it.
dropdb Options
The table below shows all the possible options when using the dropdb utility:
| Option | Description |
|---|---|
-e--echo | Prints the commands that dropdb sends to the server. |
-f--force | Attempts to terminate all current connections before dropping the database. |
-i--interactive | Prompts verification before executing database deletion. |
-V--version | Shows the utility version. |
--if-exists | Prints a notice instead of an error if the database does not exist. |
-?--help | Shows the help menu. |
-h [host]--host=[host] | Specifies the machine's hostname where the server is running. |
-p [port]--port=[port] | Specifies the TCP port where the server is listening. |
-U [username]--username [username] | Connects as the specified user. |
-w--no-password | Doesn't issue the password prompt. Useful for batch and script jobs. |
-W--password | Forces a password prompt. |
--maintenance-db=[database_name] | Specifies the database name connection. |
Conclusion
This guide showed how to drop a Postgres database using two different methods. Since both methods have use cases, it's best to know their syntax and uses.
Next, read our guide on deleting a Postgres user to see how to drop a user in multiple ways, or consider learning about the different data types in PostgreSQL.
