PostgreSQL - DROP DATABASE
Last Updated : 28 Aug, 2024
When managing databases in PostgreSQL, you may need to delete an existing database that is no longer required. The DROP DATABASE statement is designed for this purpose. It permanently removes the specified database, along with all of its catalog entries and data directories. This action is irreversible, so it must be executed with caution. Only the database owner has the authority to perform this operation, and all connections to the database must be terminated before proceeding.
Syntax
DROP DATABASE [IF EXISTS] name;
The below rules need to be followed while deleting a database:
- 'database_name': The name of the database you wish to delete.
- 'IF EXISTS': This optional clause prevents an error from being thrown if the database does not exist. Instead, PostgreSQL will issue a notice, making your operations smoother.
PostgreSQL DROP DATABASE Example
Below for the sake of example, we will be looking into our system and deleting a few databases, not in use.
Step 1: Listing Available Databases
Firstly we check for the available databases in our system using the below command:
\l
This will list our available database as below:
Here we will be deleting the highlighted databases namely:
- "my_renamed_db"
- "my_test_db1"
- "my_test_db2"
- "new_test_db"
We will delete these databases as they are no longer in use.
Step 2: Deleting a Single Database
To delete the database 'my_renamed_db', you would use the following command:
DROP DATABASE my_renamed_db;
Now if we check for the available database we will notice that the "my_renamed_db" will be missing from the list as shown in the image below:
Step 3: Deleting Multiple Databases
Now we will be deleting two databases namely "my_test_db1" and "my_test_db2" using the below commands:
DROP DATABASE my_test_db1;
DROP DATABASE my_test_db2;
After running these commands, both 'my_test_db1' and 'my_test_db2' will be deleted from your system.
Step 4: Deleting the Final Database
Now we will finally delete the last unused database using the below command:
DROP DATABASE new_test_db;
As we check our list of databases we have managed to delete all four of them as intended.
Using the dropdb Utility Program
In addition to the DROP DATABASE statement, PostgreSQL offers a command-line utility called dropdb. This utility program is a convenient way to remove a database directly from the command line, executing the DROP DATABASE statement behind the scenes.
Syntax for dropdb
The basic syntax to remove a database using dropdb is:
dropdb [options] database_name
- 'database_name': The name of the database you want to remove.
Example of dropdb
For example, to delete the 'my_renamed_db' database, you would run:
dropdb my_renamed_db
This command will have the same effect as the DROP DATABASE statement, permanently removing the specified database.
Important Rules for Deleting a Database
- Database Name Specification: Ensure that the exact name of the database you want to delete is specified after the DROP DATABASE clause.
- Using IF EXISTS: Always use the IF EXISTS clause if there's a chance the database might not exist. This prevents unnecessary errors in your SQL scripts and allows for better error handling.
Similar Reads
PostgreSQL - Copy Database Copying a PostgreSQL database is essential for tasks like testing, backup, and database migration. PostgreSQL offers efficient tools for duplicating databases either within the same server or across different servers, ensuring data consistency and integrity.This guide explains step-by-step methods f
4 min read
PostgreSQL ALTER DATABASE The PostgreSQL ALTER DATABASE statement is a powerful tool used for modifying an existing database.  The features of a database, once created can be changed using the ALTER DATABASE statement. Let's explore the syntax of PostgreSQL ALTER DATABASE, the various actions that can be performed, and pract
3 min read
PostgreSQL - Rename Database Renaming a PostgreSQL database is a simple yet essential task for developers and database administrators. Whether we're migrating data, restructuring our database, or just managing our server efficiently, the ability to rename a PostgreSQL database can streamline our workflow. In this article, we wi
4 min read
PostgreSQL - Create Database Creating a database in PostgreSQL is an important task for developers and database administrators to manage data effectively. PostgreSQL provides multiple ways to create a database, catering to different user preferences, whether through the command-line interface or using a graphical interface like
5 min read
PostgreSQL - Restore Database Restoring a PostgreSQL database is an essential process for data recovery, especially when dealing with data loss, corruption, or unintentional errors. The ability to efficiently restore a database ensures that an organization can maintain data integrity, availability, and consistency. In this artic
5 min read