PostgreSQL Drop Database with Examples

January 18, 2022

Introduction

PostgreSQL offers two command-line methods to drop a database - using the DROP DATABASE statement or a shell utility.

Removing unused databases is good practice and helps keep the workspace clean. However, keep in mind that deleting an existing PostgreSQL database removes all catalog entries and data for that database.

Continue reading to learn how to drop a database in PostgreSQL.

PostgreSQL DROP DATABASE with Examples

Prerequisites

DROP DATABASE Statement

Important: ONLY the database owner can delete a database.

The first method to remove a PostgreSQL database is to use the following SQL statement:

DROP DATABASE <database name>;

The command removes the directory containing the database information and the catalog entries. Only the database owner can execute the DROP DATABASE command. If anyone is currently using the database, the command does not execute.

To see how DROP DATABASE works, do the following:

1. Open the terminal (CTRL+ALT+T).

2. Connect to PostgreSQL:

sudo -i -u postgres psql
sudo -i -u postgres psql terminal output

3. Create a database for the example:

CREATE DATABASE example;
create database example output

The terminal prints the executed statement.

4. List all the databases with:

\l
postgres list databases example database

The database from the previous step shows up on the list.

5. Drop the database with:

DROP DATABASE example;
drop database example

The output shows the executed statement.

6. List all databases again:

\l

The example database no longer appears in the list.

IF Exists

The IF EXISTS option is open for all versions where DROP DATABASE is available. The full command syntax with the IF EXISTS option is as follows:

DROP DATABASE IF EXISTS <database name>;

The option first checks if a database exists before deleting it. If a database exists, the command drops the database. However, if a database doesn't exist, the command prints an informative notice message.

To test how the command works, follow the steps below:

1. Create an example database:

CREATE DATABASE example;

2. Drop the database using the IF EXISTS option:

DROP DATABASE IF EXISTS example;
drop database if exists example

The result is identical to using DROP DATABASE if the database does exist.

3. The database is no longer available. Rerun the command to see the output:

DROP DATABASE IF EXISTS example;
drop database does not exist message

A notice message prints stating the database does not exist.

4. To see the difference between using IF EXISTS and omitting the option, run the following command:

DROP DATABASE example;
drop database example error

Using DROP DATABASE without the IF EXISTS option on a non-existent database throws an error message.

WITH (FORCE)

The WITH (FORCE) option is available in PostgreSQL version 13 and higher.

The DROP DATABASE method won't remove the database if it's in use. If the database is in use, the terminal prints an error that a database session is open.

drop database error session

Add the WITH (FORCE) option to forcefully close the session and delete the database:

DROP DATABASE <database name> WITH (FORCE);
drop database with force

If possible, Postgres closes the user's session and deletes the database forcefully.

The dropdb Utility

The dropdb shell utility is a wrapper for the DROP DATABASE command. Effectively, the two methods are identical. However, dropdb offers additional options including removing databases remotely.

The basic syntax is:

dropdb <connection parameters> <options> <database name>

Options

The table below shows all the possible options when using the dropdb utility.

OptionTypeDescription
-e
--echo
OptionPrints the commands that dropdb sends to the server.
-f
--force
OptionAttempts to terminate all current connections before dropping the database.
-i
--interactive
OptionPrompts verification before executing database deletion.
-V
--version
OptionThe console prints the utility version.
--if-existsOptionPrints a notice instead of an error if the database does not exist.
-?
--help
OptionShow the help menu.
-h <host>
--host=<host>
Connection parameterSpecifies the hostname of the machine where the server is running.
-p <port>
--port=<port>
Connection parameterSpecifies the TCP port where the server is listening.
-U <username>
--username  <username>
Connection parameterConnect as the specified user.
-w
--no-password
Connection parameterNever issue the password prompt. Useful for batch and script jobs when no user is present.
-W
--password
Connection parameterForce password prompt. Without the option, the server loses the connection attempt if a password is necessary.
--maintenance-db=<database name>Connection parameterThe option specifies the database name connection.

For example, try the following command to see how dropdb works with the -i and -e options:

dropdb -i -e example

The program asks for confirmation before the deletion because of the -i tag.

dropdb -i -e example

Press y to confirm. The program prints the commands generated to the server. Because the database is non-existent, the program throws an error and exits.

Conclusion

After following the examples from this guide, you know how to drop a PostgreSQL database using two methods.

To learn how to drop a user in multiple ways, read our guide on how to delete Postgres user.

Next, consider learning about the different data types in PostgreSQL.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP who is passionate about programming. Her background in Electrical Engineering and Computing combined with her teaching experience give her the ability to easily explain complex technical concepts through her content.
Next you should read
PostgreSQL SELECT Statement {Syntax + Examples}
November 9, 2021

Follow this tutorial to learn how to use the SELECT statement in PostgreSQL. The statement allows you to easily process data and format the output according to your needs.
Read more
How to Export a PostgreSQL Table to CSV
March 17, 2020

Learn how to export a PostgreSQL table to a .csv file. This feature is especially helpful when transferring the table to a different system or importing it to another database application. The guide shows you how to use either...
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps. Use SQL Workbench...
Read more
PostgreSQL Vs MySQL: A Detailed Comparison
January 9, 2020

Explore the differences between the two most widely used database management systems. PostgreSQL and MySQL are both excellent database solutions, and well suited for most workloads. However, small differences...
Read more
  • © 2022 Copyright phoenixNAP | Global IT Services. All Rights Reserved.