PostgreSQL Tutorial: DROP DATABASE

Summary: In this tutorial, you will learn how to use the PostgreSQL DROP DATABASE statement to drop a database.

Table of Contents

Introduction to PostgreSQL DROP DATABASE statement

Once a database is no longer needed, you can drop it by using the DROP DATABASE statement.

The following illustrates the syntax of the DROP DATABASE statement:

DROP DATABASE [IF EXISTS] database_name;

To delete a database:

• Specify the name of the database that you want to delete after the DROP DATABASE clause.
• Use IF EXISTS to prevent an error from removing a non-existent database. PostgreSQL will issue a notice instead.

The DROP DATABASE statement deletes catalog entries and data directory permanently. This action cannot be undone so you have to use it with caution.

Only superusers and the database owner can execute the DROP DATABASE statement. In addition, you cannot execute the DROP DATABASE statement if the database still has active connections. In this case, you need to disconnect from the database and connect to another database e.g., postgres to execute the DROP DATABASE statement.

PostgreSQL also provides a utility program named dropdbthat allows you to remove a database. The dropdb program executes the DROP DATABASE statement behind the scenes.

1) Drop a database that has active connections

To delete the database that has active connections, you can follow these steps:

First, find the activities associated with the database by querying the pg_stat_activity view:

SELECT *
FROM pg_stat_activity
WHERE datname = '<database_name>';

Second, terminate the active connections by issuing the following query:

SELECT	pg_terminate_backend (pid)
FROM	pg_stat_activity
WHERE	pg_stat_activity.datname = '<database_name>';

Notice that if you use PostgreSQL version 9.1 or earlier, use the procpidcolumn instead of the pidcolumn because PostgreSQL changed procidcolumn to pidcolumn since version 9.2

Third, execute the DROP DATABASE statement:

DROP DATABASE <database_name>;

PostgreSQL DROP DATABASE examples

We will use the databases created in the PostgreSQL create database tutorial for the demonstration.

If you haven’t created this database yet, you can use the following CREATE DATABASE statements to create them:

CREATE DATABASE hrdb;
CREATE DATABASE testdb1;

1) Drop a database that has no active connection example

To remove the hrdbdatabase, use the hrdb owner to connect to a database other than hrdbdatabase e.g., postgres and issue the following statement:

DROP DATABASE hrdb;

PostgreSQL deleted the hrdbdatabase.

2) Drop a database that has active connections example

The following statement deletes the testdb1database:

DROP DATABASE testdb1;

However, PostgreSQL issued an error as follows:

ERROR: database "testdb1" is being accessed by other users
SQL state: 55006
Detail: There is 1 other session using the database.

To drop the testdb1 database, you need to terminate the active connection and drop the database.

First, query the pg_stat_activityview to find what activities are taking place against the testdb1database:

SELECT *
FROM pg_stat_activity
WHERE datname = 'testdb1';

The testdb1database has one connection from localhost therefore it is safe to terminate this connection and remove the database.

Second, terminate the connection to the testdb1 database by using the following statement:

SELECT
	pg_terminate_backend (pg_stat_activity.pid)
FROM
	pg_stat_activity
WHERE
	pg_stat_activity.datname = 'testdb1';

Third, issue the DROP DATABASE command to remove the testdb1database:

DROP DATABASE testdb1;

PostgreSQL drops the testdb1permanently.

In this tutorial, you have learned how to use the PostgreSQL DROP DATABASE statement to drop a database. In addition, you also learned how to delete a database that has active connections.