Such a case requires you to drop the database to free up the space. Thanks to this post, you will learn how the “drop” command works in deleting a database. Read on!
How to Create and Delete a Database in PostgreSQL
PostgreSQL offers numerous benefits as a DBMS. You can create a database with a single command when creating your project using it. Throughout this post, we will begin by creating a database, list the available databases, and then explain how to delete the database using the “drop” command.
1. How to Create a Database in PostgreSQL
When you have PostgreSQL installed on your system, log in using the psql shell.
Once logged in, you can create a new database with the following syntax:
Here, let’s name our database as “linux”.
You will get an output confirming that the command is executed successfully. You can verify the created database by listing all the databases on your PostgreSQL with the following command:
When you execute it, all databases will be listed. The following image confirms that we managed to create our linux database and the owner is postgres.
2. How to Delete a Database with the Drop Command in PostgreSQL
Suppose we don’t need the database that we created. We only need to use the “drop” command to delete it. Note that the “drop” command is irreversible. Once you execute it and specify the database name, your database will immediately get deleted, and all the elements, such as tables, will also get deleted. So, be sure of the action to avoid data loss.
That being said, the following syntax will help you delete a database in PostgreSQL:
Let’s delete the database that we created earlier.
If we list the available databases on our system, we can confirm that the “linux” database no longer exists. That confirms that the “drop” command successfully deleted the database.
When using the “drop” command to delete a PostgreSQL database, you can add the IF EXISTS keyword to avoid raising an error when you try to delete a non-existent database.
Let’s execute a “drop” command for a non-existent database and see which error we get:
The previous image shows that psql throws an error that the mentioned database doesn’t exist. However, if we add the IF EXISTS keyword and try to delete a non-existent database, you will notice that we don’t get a “shouting” error message.
The following is an example of using the DROP IF EXISTS command.
If you are building an application, using the DROP IFEXISTS command is the ideal option as it ensures that your application doesn’t stop or raise an interrupting error when such a situation occurs.
You can also use the “dropdb” command to delete a PostgreSQL database from the terminal. For that, you must specify the user who wants to access the database and the database name. Still, you must include the IP address of your PostgreSQL server.
For our case, our localhost contains the database and runs on port 5432. So, our “dropdb” command is as follows:
Once you enter your password, the “linux” database in this case will be deleted from your list of databases.
Conclusion
The “drop” command lets you to quickly delete a PostgreSQL database. We’ve given examples on how you can use the command to delete the existing databases and learned how to combine it with the IF EXISTS command.
