The initial step to get started with any database management system is to learn how to create a database. PostgreSQL, a feature-rich and globally used database, offers several ways to create a new database. It allows users to create a database using SQL queries as well as using a graphical user interface (GUI).

Once a database is created, you can perform any specific operation on that database like creating tables, views, sequences, performing crud operations, and so on.

This article explains multiple ways of creating a database in PostgreSQL.

• How to Create a Database Via "CREATE DATABASE"
• How to Create a Database Via "createdb"
• Create a Postgres Database Manually via GUI/pgAdmin
• CREATE DATABASE Vs. createdb - What's the Difference
• Final Thoughts

So, let’s get started!

How to Create a Database Via "CREATE DATABASE"

In PostgreSQL, the “CREATE DATABASE” statement is used to create/make a new database. For this purpose, the basic syntax is tailored as:

CREATE DATABASE databasename;

Let’s have a look at the below-listed points to understand what we have learned from the above snippet:

• The “CREATE DATABASE” is a predefined command to create a database and the “databasename” is a user-defined database name.
• From the above-given snippet, we can observe that the command is written in uppercase while the database name is written in lowercase.
• We can write the command in lowercase as well. However, it is preferred to use the uppercase syntax for reserved keywords/commands and lowercase (or camel case) syntax for the user-defined names/attributes.

Important: To create a database, you must be a superuser, or you must have "create database" privileges.

Note: To create a PostgreSQL database, we will execute the “CREATE DATABASE” command from the psql(SQL Shell). You can execute the same command from pgAdmin's query tool to create a database.

How to List the Databases?

Open the psql (SQL Shell) and execute the “\l” command to see the list of all databases:

\l

From the above-given snippet, we can observe that by default, we have three databases “postgres”, “templete0”, and “template1”.

Creating a New PostgreSQL Database

You can type the below-given command in the psql to create a user-defined database named “example”:

CREATE DATABASE example;

The response verifies that the database has been created successfully. Also, we can verify database creation by executing the “\l” command:

\l

How to Create a Database Via "createdb"?

In PostgreSQL, you can use the “createdb” command to create/make a new database. You can run the "createdb" command directly from the Command Prompt, unlike the “CREATE DATABASE” command. The “createdb” command can add some comments/descriptions to the database altogether.

The basic syntax of the createdb command will go like this:

createdb [argument/option...] [databaseName [description]]

Let’s consider the below-listed points for a profound understanding of the createdb command:

• createdb: it is a command that creates a new database in PostgreSQL.
• option: it represents a list of command-line arguments that a createdb command can accept.
• databaseName: it is a user-defined database name.
• description: it associates an optional comment/description with the newly created database.

Command-line Options/arguments

You can use any of the below-illustrated command line arguments with the createdb command to create a database with a specific purpose:

• "–help": It is used to get help regarding createdb arguments from SQL Shell.
• "-D": It is used to specify the tablespace for a new database.
• "-e": It displays all the commands sent to the server by the createdb command.
• "-E": It defines the character encoding to be applied in the database.
• "-h": It shows the server’s hostname.
• "-I ": It determines which locale will be used in the database.
• "-O": It specifies the user who will own the database.
• "-p": It specifies the TCP port that a server utilizes to listen for the connections.
• "-T": It specifies which database will be used as a template to generate a fresh database. If you didn’t specify the -T argument, then by default, a new database will be created using the “template1”.
• "-U": It specifies which user name to be used for the connection.
• "-w" Restricts createdb command to skip the password prompt.
• "-W": Restricts the createdb command to ask for the password before establishing a connection to a database.

Let’s jump into the practical implementation of the createdb command.

How Does the createdb Command Work in PostgreSQL?

Firstly, go to the directory where PostgreSQL is installed and copy its bin directory’s path. After that, open the Command Prompt and go to the bin directory of PostgreSQL using the “cd” command:

Once you are in the bin directory, execute the “createdb” command to create a new database:

createdb -U postgres exampledb;

In this example, we utilized the “createdb” command followed by the -U argument that will create a database using the default user i.e. “postgres”. While “exampledb” is the user-defined name for the database:

Note: When we executed the above-given command, it asked for the password of the “postgres” user. We entered the password and pressed the enter button to create the database.

Let’s verify whether the database has been created or not using the following command:

\l

Create a Postgres Database Manually via GUI/pgAdmin

pgAdmin is a GUI-based tool for Postgres that is open-source and freely available for different platforms like Windows, MacOS, etc. It can be used to create a new database with or without executing any query. Follow the below-provided steps to create a new database without executing any SQL queries:

Step 1: Locate Databases Tab

Launch the pgAdmin, and navigate to the "databases" section under the servers, as shown below:

Step 2: Create Database

Right-click on the database tab to open the menu, hover over the "create" option, and click the "create database..." option:

Upon doing so, a new window will pop up asking you to provide database details like its name, owner, etc. Specify the required details and hit the "Save" button:

Step 3: Verify Database Creation

Now verify the database creation by simply navigating to the "Databases" section and listing down the available databases:

The newly created database can be seen in the list of available databases.

Important: Have you ever encountered a "database databaseName already exists" error while creating a database? No worries! you can fix it by following the linked guide.

CREATE DATABASE Vs. createdb - What's the Difference?

The difference between these commands is that the “createdb” command can run directly from the command prompt while “CREATE DATABASE” cannot. Moreover, createdb can add some comments/descriptions to the database altogether.

Final Thoughts

To create a database in PostgreSQL, you can execute the commands “CREATE DATABASE” and “createdb” from psql and CMD, respectively. You can execute the “CREATE DATABASE” command from pgAmin's query tool as well. In addition to this approach, pgAdmin can also be used to create a database manually (without executing any query).

All these approaches have their significance, users can use any of these approaches according to their preferences. This write-up has considered some examples to explain the working of createdb and CREATE DATABASE commands.