September 23, 2023
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 dropdb
that 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
procpid
column instead of thepid
column because PostgreSQL changedprocid
column topid
column 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 hrdb
database, use the hrdb
owner to connect to a database other than hrdb
database e.g., postgres
and issue the following statement:
DROP DATABASE hrdb;
PostgreSQL deleted the hrdb
database.
2) Drop a database that has active connections example
The following statement deletes the testdb1
database:
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_activity
view to find what activities are taking place against the testdb1
database:
SELECT *
FROM pg_stat_activity
WHERE datname = 'testdb1';
The testdb1
database 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 testdb1
database:
DROP DATABASE testdb1;
PostgreSQL drops the testdb1
permanently.
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.