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:
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:
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:
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
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:
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:
\lThis 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:
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:
Step 2: Provide Login Credentials
Upon clicking the “pgAdmin”, a new window will pop up asking you to provide the superuser/master password:
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”:
Upon doing so, you will be navigated to the following window:
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:
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:
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:
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:
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:
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:
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.
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.
