JIYIK CN >

There are two ways to access PostgreSQL objects and databases on your system. One is through an interface, such as a graphical interface like PGADMIN, and the other is the basic command line tool psql.

Today, we will look at DROP DATABASEthe Psql version of the issue command and how it works in PGADMIN.

DROPDelete the database via PSQL using the command

First, open 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

If you don't understand what's going on, we recommend you read Psql Setup and Commands first.

You will be asked to enter Postgres PASSWORD, once completed, press Enter.

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

\l

This command will list all of your databases currently on the server.

As a side note, make sure not to modify or delete any database you are not aware of as this could be harmful and result in loss of work. Follow the tutorials and don't venture into unknown territory.

So in this tutorial, the current database is:

Output:

Suppose we want to have DROP postgresa database. We can achieve this as follows.

DROP DATABASE postgres;

If userthat doesn't work, another command you can issue is to use the following command.

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

Be sure to include a semicolon at the end.

This command will open DROPyour database and work efficiently. However, DATABASEit will return an error if it is currently opened or was last accessed by multiple users.

ERROR:  cannot drop the currently open DATABASE

Therefore, please close your database and save your work before deleting it.

Use DROPDBas a shell alternative to the PSQL command

Many users issue DROPDBcommands from Psql instead of CMD (Command Prompt), which is a mistake.

You have to open CMD to run this command against DROPthe database. Let us learn how to do this.

Search Windows for environment variables. In the box below, click 环境变量.

Click NEWto add a new value path.

In VARIABLE PATH, enter the and folders BINof , which would be:PATHLIB

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

Press Enter and close.

In CMD, enter BINthe folder using the following command.

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

Issue the following command.

DROPBD [DATABASE NAME]

This command will delete the database as needed. All of the above is to ensure that your system DROPDBdoes not have errors when issuing the command.

Force disconnect and use DROPDBas a shell alternative to PSQL commands

We assume that your system does not have environment variables set yet. If you do, leave them as is or edit them to the following configuration.

If you want DROPto use forced disconnection of the database when issuing the command, you can use the keyword in the shell CMD -fand use it in the database FORCE.

For the command in CMD DROP:

DROPBD -f [DATABASE NAME]

For the database interface or the PGADMIN DROPcommand, use the following command:

drop database postgres with (FORCE)

FORCEIt DROPis essential to terminate all existing connections to the database before invoking the command.-f

Alternatively, to sever all existing connections, you can continue by issuing the command with PROCESS TERMINATION:

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

Using it will terminate the current session of Postgres server running in the background. After this, you can issue DROPcommand to drop the database.

DROPAnother way to issue the command is pg_hba.confto restrict access to the database through a configuration file. However, this is least recommended.

It is best to restart the server and call DROPthe command to sever the connection. Also, make sure that no other users are operating on the database, as they will be lost once the database is deleted.

Use DROPDBShell Alternative for PSQL Command as Ubuntu Users

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

Connection Request:

sudo -i -u postgres psql

Use \lto view available databases.

postgres=#  \l

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

IF EXISTSYou can use the command to check if the database already exists before deleting it , because if it does not exist, DATABASE DROPthe command will return an error.

Following are the different ways to use Postgres commands:

For example, -ithe command will ask for confirmation before deleting. Therefore, you can use different syntaxes as needed.

Also, remember that once the database is deleted, we cannot recover it. That is why we have to use the confirmation flag before issuing the command.

in conclusion

We hope that you learned today about DROPdifferent ways to issue commands for databases present in PostgreSQL. These can be used in any way you need.

However, in some cases it is better to check if all prerequisites are present, for example in case of CMD shell, environment variables must exist beforehand.