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 (Javatpoint) by a left-click on it.
Then right-click on the Javatpoint
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:

Where the command contains the following parameters:

Parameters	Description
IF EXISTS	It is an optional parameter; where the warning is displayed in the place of an error if the database does not exist.
name	Here, we will reference the database name that we want to drop it.

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.

Step 2

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

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

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

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

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

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 = 'Javatpoint';

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:

where the option could be the following:

Options	Description
-e	Here e means Echo, which is used to create and send to the server.
-i	It is used to show the verification prompt before operating any fatal job.
--help	It allows us to help with dropdb command-line statements.
-h host	It defines the name of the host of the system, where the server is directly executing.
-p port	This option defines the Unix domain socket file extension, where the server is creating the connections.
-V	we can use -V option, to print the dropdb version.
-U username	It is used to display the user name.
-w	if we don't need a password screen, we can use this option.
maintenance db-=dbname	To connect the database for dropping the target database, we will use this option to describe the database name.
--if exists	This option will display an error rather than a warning if the database does not exist.
-W	This option is used to prompt for a password before dropping the database.

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

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

Here, we will use the Postgres username to drop the database. And the above command drops the javatpoint 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.

Next TopicPostgreSQL Create Table

← prev next →