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.
dropdatabase

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.
dropdatabase

See the result

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

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:

ParametersDescription
IF EXISTSIt is an optional parameter; where the warning is displayed in the place of an error if the database does not exist.
nameHere, 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.

dropdatabase

Step 2

  • For dropping the database, we will enter the below command:
  • While using this above command, we may encounter this below error:

dropdatabase

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:

dropdatabase
  • 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:
dropdatabase

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:
dropdatabase

Step 4

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

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:

OptionsDescription
-eHere e means Echo, which is used to create and send to the server.
-iIt is used to show the verification prompt before operating any fatal job.
--helpIt allows us to help with dropdb command-line statements.
-h hostIt defines the name of the host of the system, where the server is directly executing.
-p portThis option defines the Unix domain socket file extension, where the server is creating the connections.
-Vwe can use -V option, to print the dropdb version.
-U usernameIt is used to display the user name.
-wif we don't need a password screen, we can use this option.
maintenance db-=dbnameTo connect the database for dropping the target database, we will use this option to describe the database name.
--if existsThis option will display an error rather than a warning if the database does not exist.
-WThis 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:

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.





Latest Courses