Databases in Postgres contain database objects. These objects, such as tables, store the data efficiently and securely that can be retrieved when needed. Postgres offers a default database named “postgres”, however, users can create new databases of their choice. There is a huge probability that some already-created databases are of no use to us now. In such cases, we can simply delete/drop them to free some space.
This post will teach you how to:
Let’s get started with the learning.
How to Drop a Database in PostgreSQL?
We can delete/drop the database that is not useful for us. We can do this particular task by using different ways including psql and pgAdmin. We will discuss these one by one.
Method 1: Drop a Database in psql
We can drop a database by querying the DROP DATABASE command in psql or pgAdmin. This DROP command can be queried in two ways.
Drop a Database in psql Using DROP Statement
We can simply delete the database by using the DROP statement. The basic syntax of the DROP statement is:
DROP DATABASE db_name;
The above syntax simply requires the name of the database that needs to be deleted, after the DROP DATABASE command.
But first, we will see all the existing databases in our system before proceeding with the deletion. Execute the following meta-command to fetch the list of all the databases:
\lThis will return the list of databases as:
Now let’s try to drop any of the databases.
Example 1: Drop Database Using DROP Statement in psql
Let’s suppose we want to remove/drop the database named “test_db”. The first step to do so is to open the psql and enter the password after pressing the enter key for the first 4 default fields. After entering the password, we will write the following query to delete the test_db database:
DROP DATABASE test_db;
This will simply delete the database and this can be verified by the following output:
The “DROP DATABASE” message shows that the database has been dropped.
We can also verify this by executing the \l command. The output is given as:
We can see that the test_db is not present in the list hence it is deleted.
Example 2: Dropping a Database With Active Connections in psql
If we try to delete the database that has some active connection or has some database object depending on it, we will find some trouble by deleting it.
For example, if we try to delete the “database_1” database, that contains a table, we will encounter an error like this:
DROP DATABASE database_1;
So in this case, where the database has an active connection, you need to delete the active process first and then you can delete the database. To do this, we will execute the following query:
SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'database_1';
You can customize the query with the name of your database. The consequence of this query is that it will terminate all the active processes. The output like this will ensure the process/processes termination:
Now if we try to drop the database using the command:
DROP DATABASE database_1;
This query will now successfully drop the database like this:
So this is how we can delete a database with an active connection and we can verify it again by executing the “\l” command.
Drop a Database in psql Using DROP IF EXISTS Statement
We have seen the detailed demonstration of the DROP statement in PostgreSQL. The simple DROP statement will throw an error if we try to delete the database that does not exist. This generally happens when we want to delete a non-existing database by mistake. As a solution to this issue, we use the DROP IF EXISTS statement to drop a database.
Let’s try deleting a database that does not exist named “non_exisiting_database” like this:
DROP DATABASE non_exisiting_database;
This will return an error that looks like this:
We can see that the error is thrown by the query. But if we do the same thing using the DROP IF EXISTS statement the query will not throw an error. The query can be written as:
DROP DATABASE IF EXISTS non_exisiting_database;
The output looks like this:
We can see that the query has not thrown an error instead it has simply raised the notice. So what a DROP DATABASE IF EXISTS statement does is, it deletes the database if it exists. If not, it will not throw an error rather it will simply raise a notice and skip the statement.
These are the ways we can drop a database in PostgreSQL using psql. Let’s see how we can drop the database in pgAdmin.
Method 2: Drop a Database in pgAdmin
Same as psql, we can drop databases in pgAdmin as well. We can manually delete the Postgres database using the pgAdmin.
Dropping a Database Manually in pgAdmin
The pgAdmin can be used to drop a database as it can manually delete the database.
Example 1: Dropping a Database Manually in pgAdmin
To drop a database in pgAdmin, you first need to open the pgAdmin. The application will ask you to enter your password. After entering the password you will enter a window having a side panel showing some options while the right side will be empty like this:
From the side panel, we will have to select the database that we want to delete and right-click on that specific database name. A dropdown menu will appear. Select the delete/drop option from the menu like this:
After clicking the “delete/drop” the confirmation window will open. Click “Yes” to drop the database.
After clicking the “yes” button, the database from the side panel will disappear. Which ensures the successful deletion of the database. What if the database has some active connections?
Example 2: Dropping a Database With Active Connections in pgAdmin
We have a database “new_database” which has active connections. Let’s try to delete the database manually, for that just follow the same above-mentioned steps. When you hit the” Yes” button, the error messenger will appear:
This error says that the new_database is an active connection. Press “OK” to continue.
We can terminate the sessions that are using the new_database by using the following query:
SELECT
pg_terminate_backend (pg_stat_activity.pid)
FROM
pg_stat_activity
WHERE
pg_stat_activity.datname = 'new_database';
This will terminate all the active connections of this database and eventually the following message will appear:
This ensures that all the active connections have been terminated. Press the “Cancel” button.
Now again try to delete the “new_database” manually. This time after confirming the deletion, we will see that the database will be deleted and disappear from the databases option in the side panel.
This is how we can delete/drop the database from the pgAdmin
Conclusion
We can drop a database in Postgres by executing the DROP DATABASE or DROP DATABASE IF EXISTS command in psql or pgAdmin. Moreover, we can drop the database in pgAdmin by manually deleting it from the side panel. We can face trouble deleting the database that has an active connection in both psql and pgAdmin. For that, we will first have to terminate the active connections and then delete the database. In this article, we have seen all the possible methods to drop databases in psql and pgAdmin.
