PostgreSQL DROP DATABASE Statement

Summary: in this tutorial, you’ll learn how to drop a database using the PostgreSQL DROP DATABASE statement.

PostgreSQL DROP DATABASE statement overview #

The DROP DATABASE statement permanently deletes a database, including all its objects such as tables, views, indexes, and data. This action is irreversible.

Here’s the basic syntax of the DROP DATABASE statement:

DROP DATABASE database_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the statement removes the database named database_name.

When you connect to a database, it becomes the current database.

PostgreSQL does not allow you to drop the current database. To drop a database, you must connect to a different database. Additionally, only the database owner or superuser can drop a database.

However, if the database has action connections the DROP DATABASE will fail.

If you want to drop has active connections, you can terminate them before deleting the database using the with FORCE option:

DROP DATABASE database_name
WITH (FORCE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dropping a database #

First, open the terminal and connect to your PostgreSQL database server using psql tool:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new database named crm:

CREATE DATABASE crm;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, switch to the crm database:

\c crmCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, drop the crm database:

DROP DATABASE crm;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: cannot drop the currently open databaseCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To drop the crm database, we need to switch to a different database.

Fifth, switch to the postgres database:

\c postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, drop the crm database:

DROP DATABASE crm;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dropping a database with the FORCE option #

We’ll create a new database, connect to it, and drop the database from a separate session.

Session 1 #

First, connect to your PostgreSQL server using psql tool:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new database named finance:

CREATE DATABASE finance;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, switch to the finance database:

\c financeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Session 2 #

First, connect to your PostgreSQL in a different session:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, attempt to drop the finance database:

DROP DATABASE finance;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: database "finance" is being accessed by other users
DETAIL: There is 1 other session using the database.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, check the active connections using the following query:

SELECT
  pid,
  usename,
  application_name,
  client_addr
FROM
  pg_stat_activity
WHERE
  datname = 'finance';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

  pid  | usename  | application_name | client_addr
-------+----------+------------------+-------------
 30400 | postgres | psql             | 127.0.0.1Code language: plaintext (plaintext)

Finally, drop the finance database with the FORCE option:

DROP DATABASE finance WITH (FORCE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

DROP DATABASECode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the statement successfully closes the active connection and deletes the database.

Session 1 #

If you attempt to run any query on session 1 connected to the finance database that has been deleted, you’ll encounter an error. For example:

SELECT 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the DROP DATABASE statement to delete a database from the PostgreSQL server.
Use the DROP DATABASE with the FORCE option to close active connections and drop the database.
Only the superuser and database owner can drop the database.

Was this tutorial helpful ?