PostgreSQL Create Database

In this section, we are going to discuss how we can create a database in PostgreSQL.

In PostgreSQL, we can create a database in two ways:

• PostgreSQL Create Database using pgAdmin
• PSQL Create Database Command Line (SQL Shell)

Creating Database using pgAdmin

To create a database in pgAdmin, we are going to follow the below steps:

Step 1

• Firstly, we will open the pgAdmin in our local system and then in the Object tree, we will right-click on the Databases and select Create then select database

Databases → Create → Database

Step 2

• After that, the create database window will open where we need to provide some necessary details (Database name, Comment) for creating a database and then click on the Save

Step 3

• The database is created and display in Object tree as we can see in the below screenshot:

Step 4

• And the right-hand side window will give us the SQL which is used to create the Database as we can see in the below image:

Syntax to create a database

The complete Syntax for creating a Database in PostgreSQL is as following:

CREATE DATABASE db_name
OWNER =  role_name
TEMPLATE = template			
ENCODING = encoding			
LC_COLLATE = collate			
LC_CTYPE = ctype
TABLESPACE = tablespace_name
CONNECTION LIMIT = max_concurrent_connection

In the above syntax, we have the following parameters:

Errors

We can encounter the below errors while we are using the create database command:

• No such file in the server is executing locally and accepting connections on the Unix domain socket: If we use the create database command and the server is not started then we get the error.
• Permission is not granted to create a database: This error will occur when we need to give authorization to the related users for using the create command. And the PostgreSQL account is created but does not have access to create a database.
• Create Database command is not found: If PostgreSQL is not installing correctly, we may encounterthis type of error, and we need to execute the Create Database command from our PostgreSQL installation path.

Create Database Command Line (SQL Shell)

To create a database in the command line, we are going to follow the below steps:

Step1

• Firstly, we will open the SQL shell in our local system. For this, we will go to the home button and search for pSQL and open it.

Step2

• Once the SQL shell is opened, we will press the enter key for the 4-5 times and then provide the password for the user (which we created earlier) to connect the database as shown in the below screenshot:

Step 3

After that, enter the below command to create a database

CREATE DATABASE TheDeveloperBlog;

Step 4

To get a list of all databases created earlier, we will enter the below command:

\l

Step 5

To connect to a database, we will enter the below command:

\c TheDeveloperBlog

• As we can see in the above screenshot that we are connected to database TheDeveloperBlog through the command prompt.
• And we can also execute various commands such as Trigger, Create Table, and so on.