How to Drop Database Through PSQL in PostgreSQL

1. Use the DROP Command to Drop Database Through PSQL
2. Use DROPDB as a Shell Alternative to PSQL Commands
3. Conclusion

There are two ways of accessing PostgreSQL objects and the database in your system. One is through an interface, something graphical such as PGADMIN, and the other is a basic command-line tool, psql.

Today, we will be looking at the Psql version of issuing the DROP DATABASE command and how it works in PGADMIN.

Use the DROP Command to Drop Database Through PSQL

First, open the Psql from the file path below.

C:/Program Files/PostgreSQL/14/bin (instead of 14, you might have a different version)

Open CMD from the search bar, and run the command:

C:\Users\[Your User]>cd C:/Program Files/PostgreSQL/14/bin

Now, type the following:

C:\Program Files\PostgreSQL\14\bin>psql -U postgres

In case you do not understand what is going on. We recommend you read up on the Psql setup and commands first.

You will be asked to enter the PASSWORD for Postgres, and once done, press Enter.

You will now be inside your PostgreSQL server. To view all your databases, write out the following command:

\l

This command will list all your current databases on the server.

As a side note, make sure not to modify or delete any databases you do not know about, as that may be harmful and cause a loss of work. Follow out the tutorial and do not venture into unknown territories.

So in this tutorial, the current databases are:

Output:

Suppose we want to DROP the postgres database. We can implement this as shown below.

DROP DATABASE postgres;

Another command you can issue if the user does not work is to use the following.

psql -U <user>  -c "drop DATABASE [DATABASE NAME]"

Make sure to include a semicolon at the end.

This command will DROP your database and work effectively. However, if the DATABASE is currently open or accessed by multiple users at the end, it will return an error.

ERROR:  cannot drop the currently open DATABASE

Thus, close off your database and save your work before dropping it.

Use DROPDB as a Shell Alternative to PSQL Commands

Many users will issue the DROPDB command from Psql rather than the CMD (Command Prompt), which is wrong.

You have to open CMD to run this command to DROP the database. Let us learn how to do this.

Search for Environment Variables in Windows. In the following box, click on Environment Variables.

Click on NEW to add the path to the new values.

In the VARIABLE PATH enter the PATH for the BIN and the LIB folder which will be:

C:\Program Files\PostgreSQL\14\bin
                  C:\Program Files\PostgreSQL\14\lib

Press Enter and close.

In CMD, enter the BIN folder using the command below.

cd C:\Program Files\PostgreSQL\14\bin

Issue the following command.

DROPBD [DATABASE NAME]

This command will drop the database as you need. All the above was to ensure that your system does not run into an error while issuing the DROPDB command.

Force Disconnect and Use DROPDB as a Shell Alternative to PSQL Commands

We assume your system does not have the environment variables already set up. If you do, keep them as it is or edit them to the following configurations.

If you want to use force to disconnect the database while issuing the DROP command, you can use the -f keyword in a shell CMD and FORCE in the database.

For the DROP command in CMD:

DROPBD -f [DATABASE NAME]

And for the DROP command in the database interface or PGADMIN, use the following:

drop database postgres with (FORCE)

FORCE terminates all existing connections with the database and is essential before calling the DROP command. The same goes for -f.

Alternatively, to cut off all existing connections, you can go ahead and issue the PROCESS TERMINATION command using the following:

select pg_terminate_backend([process id]) from pg_stat_activity where datname='database name';

Using this will terminate the current session of the Postgres server running in the background. After this, you can issue the DROP command to delete the database.

Another way you can issue the DROP command is by restricting access to the database by configuring the pg_hba.conf file. However, this is least recommended.

It is always better to cut off connections by restarting the server and calling the DROP command. Also, make sure that no other users have work on the database that will be lost once the database is dropped.

Use DROPDB as a Shell Alternative to PSQL Commands for Users in Ubuntu

For users in Ubuntu, you can issue the following commands.

Connection Request:

sudo -i -u postgres psql

Using \l to view the databases available,

postgres=#  \l

And using the DROP DATABASE [database_name] command, you can drop the database.

You can check if the database already exists using the IF EXISTS command before dropping it because if it does not, the DATABASE DROP command will return an error.

Below are the different ways you can use the Postgres command:

For example, the -i command will ask for confirmation before deletion. Hence, there are different syntaxes you can use as you see fit.

Also, remember that once the database is dropped, we cannot revert it. That is why we must use the confirmation flag before issuing the command.

Conclusion

We hope you learned today the different ways to issue the DROP command for databases present in the PostgreSQL database. These can be used in any way required.

However, in some instances, it is better to check if all the pre-requisites are present, like in the case of the CMD shell, where the environment variables must be present beforehand.