In PostgreSQL the DROP DATABASE statement is used to delete an existing database. It deletes the cataloged entries and data directory completely. It is an irreversible process. It can be performed by only the database owner and before executing this statement all connections to the database must be terminated.
Syntax: DROP DATABASE [IF EXISTS] name;
The below rules need to be followed while deleting 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.
Below for the sake of example we will be looking into our system and deleting a few databases, not in use.
Firstly we check for the available databases in our system using the below command:
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” and “new_test_db” as they are not in use.
So to delete the “my_renamed_db” use the below command:
DROP DATABASE my_renamed_db;
Now if we check for the available database we will notice that the “my_renamed_bd” will be missing from the list as shown in the image below:
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;
This will result in the deletion of both the databases as depicted below:
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.
Note: 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.