PostgreSQL Delete/Drop Database

The Drop/delete command is used to eternally delete all the file entries and data directory from the PostgreSQL platform. Therefore, we have to use this command very carefully.

In this section, we are going to learn how to drop or delete the database, which we don't need any more in PostgreSQL.

In PostgreSQL, we can drop the database in two ways:

• Drop Database PgAdmin
• Drop Database Using SQL Shell

Drop Database PgAdmin (Graphical User interface)

To create a database in pgAdmin, we are going to follow these below steps:

Step 1

• Firstly, we will open the pgAdmin in our local system.
• Select the database (TheDeveloperBlog) by a left-click on it.
• Then right-click on the TheDeveloperBlog
• After that, click on Delete/dropoption from the given drop-down list to delete the database.

Step 2

• Once we click on the Delete/Drop option, one confirmation pop-up will appear on the screen, where we click on the Yes button to drop the database.

See the result

• As soon as we click on the Yes button, the database is deleted immediately from the record.

Drop Database Using SQL Shell (Command Line)

In this, we are going to drop the database in the SQL Shell (Command Line).

Syntax

The syntax for Dropping the database is as follows:

Syntax:

DROP DATABASE [ IF EXISTS] name;

Where the command contains the following parameters:

Now, we are going to use this command in the command line:

Step 1

• Open the SQL shell and type the below command to see the existing database.

\l

Step 2

• For dropping the database, we will enter the below command:

Drop database TheDeveloperBlog;

• While using this above command, we may encounter this below error:

ERROR: database "TheDeveloperBlog" is being accessed by other users
Detail: There is 1 other session using the database.

To delete the TheDeveloperBlog database, we need to follow the below process:

• First, we have to Revoke the connection with the help of below command:

REVOKE CONNECT ON DATABASE TheDeveloperBlog from public;

• And then press the Enter key.
• Once the connection is Revoked, we will enter the following query:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TheDeveloperBlog';

• Then enter the drop database query and use the \l command to verify whether the database is deleted or not as we can see in the below screenshot:

Step 3

• If we try to drop the same database again, we will get the following error as we can see in the below screenshot:

Step 4

• Then we are going to drop the database with IF exist condition, and we get the below warning:

Using dropdb command

In PostgreSQL, the dropdb is a command-line executable command which covers the SQL drop database command. This command can only be run by those end-users who is the owner of the database or a database superuser. We can remove the database remotely with the help of the dropdb statement.

Syntax

The syntax for dropdb is as following:

dropdb [option...] dbname

where the option could be the following:

Let us see an example where we are deleting a database from Operating system command prompt:

dropdb -h localhost -p 5432 -U postgress TheDeveloperBlog
Password for user postgress: ****

Here, we will use the Postgres username to drop the database. And the above command drops the TheDeveloperBlog database.

Overview

• The drop database command is used to delete all the file entries and data directory permanently from the PostgreSQL platform.
• With the help of the pgadmin tool, we can also drop the database
• We can remotely drop a database by using the dropdb command.