PostgreSQL Drop Databases

If you don’t need a database, you can drop the database. In PostgreSQL, the DROP DATABASE statement is used to drop a database.

Note : The DROP DATABASE statement will permanently delete the database and all tables in the database, please proceed with caution.

PostgreSQL `DROP DATABASE` syntax

To execute DROP DATABASE statements, you need to be superuser or the owner of the database.

The following is the syntax of the PostgreSQL DROP DATABASE statement:

DROP DATABASE [IF EXISTS] database_name;

In the above syntax:

The database_name is the name of the database to drop. You need to provide the name of an existing database.
If you provide a database name that does not exist, PostgreSQL will report an error. You can use the IF EXISTS option to prevent this error. PostgreSQL will give a notification for a database that does not exist.

You cannot drop a database that still has active connections. At this point, PostgreSQL will give an error report. You can use the pg_terminate_backend() function to terminate active connections before dropping this database.

PostgreSQL DROP DATABASE instance

In this example, we will use the psql tool to connect to the PostgreSQL server, create a database test_db and drop it.

First, log in to the PostgreSQL server as a user with privileges:
```
[~] psql -U postgres
psql (14.4)
Type "help" for help.
```
Second, create the test_db database:
```
CREATE DATABASE test_db;
```
```
CREATE DATABASE
```
If you enter an already existing database name, PostgreSQL will return an error: ERROR: database "test_db" already exists.

Then, connect to the test_db database you just created using the \c command:

\c test_db;

You are now connected to database "test_db" as user "postgres".

Then, try to drop the test_db database using the follow statement:
```
DROP DATABASE test_db;
```
```
ERROR:  cannot drop the currently open database
```
That is, you cannot drop a currently open database.
Keep the session just now, open a new session and log in, then try to drop the test_db database:
```
DROP DATABASE test_db;
```
```
ERROR:  database "test_db" is being accessed by other users
DETAIL:  There is 1 other session using the database.
```
Here, PostgreSQL reports an error: The database test_db is being accessed by another user.
Close the previous session, go back to this session, and try dropping again:
```
DROP DATABASE test_db;
```
```
DROP DATABASE
```
At this point, the database has been deleted.

Show and terminate active connections to the database

Sometimes, it is not yourself to connect to the database to drop. You can query active connections to the database from the pg_stat_activity view as follows:

SELECT
  pid,
  usename,
  application_name
FROM
  pg_stat_activity
WHERE
  datname = 'test_db';

 pid  | usename  | application_name
-------+----------+------------------
37771 | postgres | psql
(1 row)

Then, use the pg_terminate_backend() function to terminate the active connection with pid 37771:

SELECT pg_terminate_backend(37771);

pg_terminate_backend
----------------------
t
(1 row)

Then, drop the database using the DROP DATABASE statement.

Conclusion

The PostgreSQL DROP DATABASE statement is used to drop an existing database. Dropping a database is a dangerous operation, so be careful.