PostgreSQL DROP DATABASE with Example

The DROP DATABASE statement removes all the catalog entries and data directory permanently from the PostgreSQL environment. So, you should be extra cautious when performing this operation.

However, this command can be executed only by the database owner. You also can’t execute this command while someone is connected with the target database. You need to connect to some other database to execute the DROP DATABASE command.

Syntax:

DROP DATABASE [IF EXISTS) name;

Here:-

IF EXISTS: This is an optional parameter. In case of the database does not exist, a warning is displayed instead of an error
name: mention the DB name that you want to drop

Let’s see it in action

PostgreSQL Drop Database Using SQL Shell(Command Line)

Step 1) Use command \l to determine the currently available database.

Step 2) To drop database enter command

drop database guru99

Use command \l to check whether DB is deleted

Step 3) Try to drop the same database again, you will get an error.

Step 4) Drop the database with IF exists clause and you get a warning

drop database IF EXISTS guru99

PostgreSQL Drop Database PgAdmin (GUI)

Step 1) Right Click on database “guru99” and click “Delete/Drop.”

Step 2) Click OK on the confirmation pop-up

Database is deleted.

dropdb command

The dropdb command allows you to delete database remotely. But, the user must be the owner of the database in order to delete that database.

Syntax:

Dropdb [option.] dbname

Option	Description
-e	Echo commands that dropdb creates and sends to the server.
-i	Display a verification prompt before performing any destructive job
-V	Use this option to print the dropdb version.
–help	Provide help with dropdb command-line arguments.
-h host	Helps you to specify the name of the host of the system on which the server is currently running.
-p port	Option allows you to specify the Unix domain socket file extension on which the server is establishing connections.
–if exists	If exists will show an error instead of a warming if the DB does not exit
-U username	User name to connect as.
-w	Use this option if you don’t want a password prompt
-W	Use this parameter to prompt for a password before dropping the database.

Example

We want to destroy a database guru99 with the help of a server on host rome, port 4565, with verification and you need to use following command:

dropdb -p 4565 -h rome -i -e guru99

Database “guru99” will be permanently deleted.

Are you sure? (y/n) if you select y, then you can

DROP DATABASE guru99;

Summary

The DROP DATABASE statement removes all the catalog entries and data directory permanently from the PostgreSQL environment
Use can use If exists clause so that no error is displayed
You can also drop a database using pgadmin
dropdb command allows you to remotely drop a DB

LOGIN

PostgreSQL DROP DATABASE with Example