How to Create a Database in Postgres

PostgreSQL is an open-source and extensively used relational database that is used to safely and effectively store and manage data. It supports different tools to execute the code and queries interactively. Among these tools, the most popularly used tools are SQL Shell (psql) and pgAdmin. In order to use and perform different operations in the PostgreSQL database, first, we need to know how we can create a database in Postgres. 

In this post, we are going to look at various approaches to creating a database in PostgreSQL.

How to Create a Database in Postgres?

The default database created by PostgreSQL is “postgres”, However, we can create the database and name it as we like. For this purpose, PostgreSQL provides many methods, some of which are stated below:

  1. Create a Database Using psql
  2. Create a Database Using pgAdmin

Method 1: Create a Database Using psql

The psql is an alternative command line application for PostgreSQL that assists in running the Postgres queries, issues them to PostgreSQL, and shows the desired results. We can create a database using psql by executing the query. The fundamental syntax for creating a database is:

CREATE DATABASE dbName;

In the above syntax, CREATE DATABASE is the clause used to create a database. Following this sentence, we will specify the database name that will be created in PostgreSQL.  Follow the below-provided steps to learn database creation using psql:

Step 1: Launch the psql Application

Navigate to the Windows search bar, type “psql” and hit Enter to open the SQL Shell:

How to Create a Database in Postgres-01

Step 2: Provide Login Credentials

After entering the application, the psql will prompt you to enter the Server, Database name, Port number, Username, and password. For the first 4 fields simply press enter. The fields will pick the default values. You need to enter the password. This password is the same password as the one you chose/used when installing PostgreSQL into your system. The following will be the output after entering the password:

How to Create a Database in Postgres-02

Here the postgres=#” ensures the connection to the default database.

Step 3: Check Existing Databases

Execute the “\l” meta-command to see the existing databases:

\l
How to Create a Database in Postgres-03

Step 4: Create a New Database

To create a database having the name “database_1”, we will execute the following command in psql:

CREATE DATABASE database_1;

After the execution of this command, the psql will return a “CREATE DATABASE” message, which depicts that the database has been created. The output is given below:

How to Create a Database in Postgres-04

Step 5: Verify Database Creation

We can look for our created database in the list of databases to make sure whether the database has really been created or not. To get the list of databases in our system, the following meta-command is run in the psql:

\l

This meta-command will return the list of all the databases in the system. Note that this meta-command is not followed by any colon or semi-colon.

Following is the output of the command:

How to Create a Database in Postgres-05

We can see that the created database is present in the list of databases which clearly means that the database has been created.

Now we will move towards other methods to create the database in Pgadmin.

Method 2: Create a Database Using pgAdmin

The pgAdmin helps the PostgreSQL users to perform specific operations on their data. It provides users with a good graphical user interface to handle, manipulate, and administer data effectively. We can create a database in pgAdmin by using two approaches:

  • Create a database using Postgres queries.
  • Create a database Manually in pgAdmin.

Approach 1: Create a Database Using Postgres Queries

We can create the database using Postgres queries in the same way we did in psql. The same syntax is used to create the database in this method. Now let’s create a database with the “new_database” name. To do that, the following steps will be executed in pgAdmin:

Step 1: Open pgAdmin

Search for pgAdmin in the Windows search bar and click on the corresponding application to open it:

How to Create a Database in Postgres-06

Step 2: Provide Login Credentials

Upon clicking the “pgAdmin”, a new window will pop up asking you to provide the superuser/master password:

How to Create a Database in Postgres-07

Provide the password and hit the “OK” button to proceed.

Step 3: Open the Query Tool

Expand the “Servers” tree, navigate to the databases section, right-click on the desired database, and select “Query Tool”:

How to Create a Database in Postgres-08

Upon doing so, you will be navigated to the following window:

How to Create a Database in Postgres-09

Here you can execute and command or query of your choice to perform any specific database operation.

Step 4: Create a New Database

Now, the following query will be written in the query tool to create a new database named “new_database”:

CREATE DATABASE new_database;

This query will create a database named “ new_database” which will be ensured by the “CREATE DATABASE” message in the output, like this:

How to Create a Database in Postgres-10

Note that we can run the query from the run button mentioned above or by pressing the” F5” key.

Step 5: Verify Database Creation

We can also see the created databases from the side panel of the PgAdmin. All the databases created in the PostgreSQL are enlisted under the “Databases” option in the side panel like this:

How to Create a Database in Postgres-11

If you are unable to see the newly created database despite the “CREATE DATABASE” message returned. Just refresh the database option by right-clicking on it like this:

How to Create a Database in Postgres-12

This is how we can create a database using a query in Postgres. We can also manually create the database in pgAdmin of Postgres. Let’s see how.

Approach 2: Create a Database Manually Using pgAdmin

The above method was about creating a database using queries in Postgres, in this method we will learn to create a database manually. 

Step 1: Navigate to the Databases Section

To create the database manually, go to the “Databases” option from the side panel and right-click on it. You will see a drop-down menu from there you have to select the “Create” and the “Database” options like this:

How to Create a Database in Postgres-13

Doing this will pop up a window asking you to enter/fill certain fields such as the name of the database. On the general tab, just enter the name of the database like this:

How to Create a Database in Postgres-14

Leave all other fields in tabs as default. 

Step 2: Verify SQL Query

On the last tab named “SQL”, a SQL query will be generated according to the fields set in other tabs. If they are left default, the query will have default field values. After that press the “save” button given below to create the database:

How to Create a Database in Postgres-16

Step 3: Verify the Database Creation

The database will be created after hitting the save button and that database can be seen under the databases option in the side panel.

How to Create a Database in Postgres-17

So we can create the database manually by this method. 

Conclusion

To create a database in PostgreSQL, execute the “CREATE DATABASE” command either in the SQL Shell or pgAdmin. Users who prefer a graphical interface can also create databases manually using pgAdmin (without executing any queries). This article has explored three different ways to create a database in PostgreSQL: using the command-line tool psql, utilizing Postgres queries, and manually through the pgAdmin.

Over 500 Million Views on Our Blogs and YouTube! We’re inviting 10 businesses to partner with us and grow together. Click here to join us! ×