PostgreSQL DROP DATABASE

 Summary: in this tutorial, you will learn how to delete existing database by using PostgreSQL DROP DATABASE statement.

Introduction to PostgreSQL DROP DATABASE statement

Once a database is no longer needed, you can delete it by using the DROP DATABASE statement. The following illustrates the syntax of the DROP DATABASE statement:

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 the database owner can execute the DROP DATABASE statement. In addition, you cannot execute the DROP DATABASE statement if there is any active connection to the database. You have to connect to another database e.g., postgresqlto execute the DROP DATABASE statement.

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

Delete a database that has active connections

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

First, find the activities that are taken place against the target database, you can query the pg_stat_activityview as the following query:

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

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:

PostgreSQL DROP DATABASE examples

We will use the databases created in the PostgreSQL create database tutorial for the demonstration. If you don’t have these databases available, you can create them by executing the following statements:

Delete 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:

PostgreSQL deleted the hrdbdatabase.

Delete database that has active connections example

The following statement deletes the testdb1database:

However, PostgreSQL issued an error as follows:

To delete the testdb1 database, you need to follow the steps as described in the above section.

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

The testdb1database has 1 connection from localhosttherefore it is safe to terminate this connection and remove the database.

Second, terminate the connection to the testdb1database by using the following statement:

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

PostgreSQL deleted the testdb1permanently.

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