PostgreSQL - Drop Database

PostgreSQL - Dropping a Database

This chapter explains how to delete a database in PostgreSQL. You have two primary methods for this task:

• Using the DROP DATABASE SQL command.
• Using the dropdb command-line utility.

Warning: The DROP DATABASE command is irreversible. It permanently deletes the database, including all tables, data, and associated objects. Always back up your data before performing this operation.

Method 1: Using the DROP DATABASE SQL Command

The DROP DATABASE command removes a database entirely. It deletes the physical data files from the server and removes the database’s catalog entries.

Prerequisites

• Permissions: You must be the database owner or a superuser to execute this command.
• No Active Connections: You cannot drop a database while anyone, including yourself, is connected to it. You must connect to a different database (e.g., the default postgres database) to issue the command.

Syntax

DROP DATABASE [IF EXISTS] database_name [WITH (FORCE)];

Parameters

Parameter	Description
database_name	The name of the database you want to drop.
IF EXISTS	(Optional but recommended for scripts) Prevents an error from being thrown if the database does not exist. A notice is issued instead.
WITH (FORCE)	(Optional, available in PostgreSQL 13+) Forcibly terminates all active connections to the target database before dropping it. This can be useful but should be used with caution.

Example

To drop a database named testdb, you would first connect to another database and then run the command:

-- Step 1: Connect to the 'postgres' database using psql
-- psql -U your_user -d postgres

-- Step 2: Execute the DROP DATABASE command
DROP DATABASE testdb;

-- To avoid errors in scripts, use IF EXISTS:
DROP DATABASE IF EXISTS testdb;

Method 2: Using the dropdb Command-Line Utility

dropdb is a command-line wrapper around the DROP DATABASE command. It is often more convenient because it handles the connection details for you, connecting to the default maintenance database to perform the drop.

Syntax

dropdb [connection-options] [options] dbname

Common Options

Option	Description
dbname	The name of the database to be deleted.
-h host	Specifies the server’s hostname.
-p port	Specifies the server’s port.
-U username	Specifies the username to connect as.
-W	Forces a password prompt.
-e	Echoes the commands that dropdb generates and sends to the server.
--if-exists	Do not report an error if the database does not exist.

Example

To drop the testdb database from your operating system’s command prompt:

# This command connects to the local server as the 'postgres' user
# and prompts for a password before dropping 'testdb'.
dropdb -h localhost -p 5432 -U postgres -W testdb

If your environment variables like PGHOST, PGPORT, and PGUSER are set, you can simplify the command:

dropdb testdb