Managing databases is a fundamental skill for any database administrator or developer. This tutorial will guide you through the steps to create and delete databases in PostgreSQL. We’ll start by setting up the necessary environment and ensuring you have the required permissions. Then, we’ll dive into the commands needed to create and delete databases.

Prerequisites

Before we begin, make sure you have:

  • PostgreSQL is installed on your system.
  • Access to the psql command-line tool.
  • Superuser privileges or appropriate permissions to create and delete databases.

Step 1 – Connecting to PostgreSQL

First, let’s connect to the PostgreSQL server using the psql command-line tool. Open your terminal and run:

sudo -u postgres psql

You’ll be prompted to enter your password. After successfully logging in, you’ll see the psql prompt:

postgres=#

Step 2 – Creating a PostgreSQL Database

To create a new PostgreSQL database, use the CREATE DATABASE command followed by the name of the database you want to create. Let’s create a database named mydatabase:

CREATE DATABASE mydatabase;

After running this command, you should see a confirmation message:

CREATE DATABASE

To verify that the database has been created, list all databases:

\l

You should see mydatabase in the list:

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 mydatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

Step 3 – Connecting to the New Database

Once the database is created, you can connect to it using the \c command followed by the database name:

\c mydatabase

You should see a message confirming the connection:

You are now connected to database "mydatabase" as user "postgres".
mydatabase=#

Step 4 – Deleting a PostgreSQL Database

To delete a PostgreSQL database, use the DROP DATABASE command followed by the name of the database. Be cautious with this command as it permanently deletes all data in the database. Let’s delete mydatabase:

First, disconnect from the database (if you are currently connected to it):

\c postgres

Then, run the DROP DATABASE command:

DROP DATABASE mydatabase;

You should see a confirmation message:

DROP DATABASE

To verify that the database has been deleted, list all databases again:

\l

You should no longer see mydatabase in the list.

Conclusion

In this article, we covered how to create and delete a PostgreSQL database. We started by connecting to the PostgreSQL server, then we created a new database and verified its creation. Finally, we demonstrated how to delete the database and verify its deletion. You can now manage your PostgreSQL server on dedicated server hosting from Atlantic.Net!