PostgreSQL – How to Create a Database

Creating a database in PostgreSQL is not a big deal. You can create a database in postgresql using either a command line interface or a GUI-based tool such as pgAdmin. In this tutorial, we will see both methods of creating a database. So, let’s get started!

Create Database Using PSQL – CLI Tool

PSQL is a terminal-based front-end tool for PostgreSQL to write queries and issue them to the PostgreSQL database server. When you install PostgreSQL, you get the psql tool also, you don’t need to install it separately.

Now let’s see the syntax of creating a database in PostgreSQL. This tutorial is performed on ubuntu. However, the steps might be the same for Windows users as well.

First of all, login to the PostgreSQL command line client using the command “psql -U postgresql” or “sudo -u postgresql psql”. After the command, enter your PostgreSQL user password.

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Below is the syntax to create a database-

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ] ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

• All the parameters in the square bracket are optional and not all are so important to create a database if you are a beginner. We will see a short description of some important parameters here.
• name- the name of the database
• OWNER– The username or role of the user who will own the database. By default, the owner is the user who executes the command.
• TEMPLATE– The template’s name, or DEFAULT to use the default template (template1), from which the new database will be created.
• ENCODING– It specifies the new database’s character set encoding. It is the encoding of the template database by default.
• LOCALE– Using this, you can set LC_COLLATE and LC_CTYPE at the same time.
• LC_COLLATE– Collation order of the new database.
• LC_CTYPE– Character classification of the new database.
• CONNECTION_LIMIT– The number of concurrent connections that can be made. By default, no limit is there.

Example 1 – Database without any parameters

Now let’s create a database on the terminal.

CREATE DATABASE JD;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we are not specifying any parameter because the database will take all the default values and that’s all that we want.

Now you can check whether the database is created or not using the command \l.

As you can see, the database is created successfully.

Example 2 – Database with Parameters

Now we will create a database with a database with a different owner and a few other parameters.

CREATE DATABASE JD2
WITH OWNER=tejas
CONNECTION_LIMIT= 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the new database is created with a different owner.

Create a Database Using pgAdmin

You can easily create a database using the pgAdmin tool without writing a single command. Follow the steps given below to create a database in PostgreSQL using pgAdmin.

First, log in to pgAdmin using the master password and connect to the server. If you are not familiar with pgAdmin, you can read our tutorial on how to install and connect to the server using pgAdmin.

Now right-click on the “Databases” option and click create>database.

A new popup will appear to enter the database details. Enter the database name and other details. You can leave all other fields blank as those are not mandatory.

After filling in all the details, click on the save button. Your database will be created within a seconds. You can see the database name under the “Databases” option as shown below.

And that’s how you create a database in PostgreSQL.

Conclusion

In this tutorial, we have learned to create a database using a terminal as well as a GUI tool. Whether you choose to use the command line, a GUI tool, or a programming language, the basic steps remain the same. By following the steps outlined in this article, you can quickly and easily create a new database and start working with your data in PostgreSQL.

Reference

Postgresql official documentation.