PostgreSQL: Drop Database

PostgreSQL supports the DROP command to externally delete the database from PostgreSQL.

Use this command carefully as it drops the database permanently along with all the objects that belong to the database like tables, views, indexes, sequences, etc., which cannot be reverted.

In PostgreSQL, database can be dropped in two ways

Drop database from pgAdmin
Drop database using psql

Drop database from pgAdmin

To drop database from pgAdmin, follow these steps.

Open pgAdmin in your local system. Expand the Databases section, you will see a list of databases. Right-click on tutorials database and select Delete/Drop from the list of dropdown options to delete the database.

It will show confirmation popup before deleting the database

Once you click on the Yes button, the database will be deleted immediately. You will not find that database in the list of Databases as it is deleted permanently.

Drop Database using psql

The database can be deleted using psql also.

Syntax 

DROP DATABASE [IF EXISTS] <database_name>

Use the IF EXISTS option to drop the database only if it exists. If the specified database does not exist, then it will give a warning and skip the drop command.

Open SQL Shell (psql) and make a database connection. Give the \l command to view a list of all databases, as shown below.

Now, give the following command to drop a database.

drop database tutorials;

You may receive bellow error while dropping database if database you try to drop has another active sessions connected to it.

You can explicitly ask users to disconnect from the database before dropping the database or revoke connection access on a database from the public and then try to drop the database.

Example: Disconnect Database

REVOKE CONNECT ON DATABASE tutorials from public;

Now execute the drop database command again, drop database tutorials to drop the database. You can validate it by running a command to show the list of all databases.

If you try to drop a database that does not exist, you will get the following error.

Use the IF EXISTS clause along with the drop database to drop the database only if it exists, otherwise, just show the warning and skip the drop command.