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

PostgreSQL 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
PostgreSQL Create Database

Step 3

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

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:
PostgreSQL Create Database

Syntax to create a database

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

In the above syntax, we have the following parameters:

ParametersDescription
db_nameWe will use this parameter to specify the new database name, which we want to create. And we also ensure that the database must be unique because If we try to create a new database with the same name as an existing database, it will show an error.
role_nameIt is used to describe the role name for the user who will have the new database, and by default, it is postgres.
TemplateWhile creating the new database, we will require database template name.
EncodingIt is used to describe the character set encoding for the new database, and by default, it is UTF8.
CollateIt is used to define the sort order of strings that mark the result of the ORDER BY clause if we are using a SELECT statement.
CtypeThis parameter is used to display the character classification for the new database.
tablespace_nameIt is used to define the tablespace name for the new database, and by default, it is the template database's tablespace.
max_concurrent_connectionThis parameter is used to define the maximum parallel connections of a new database, and by default, it is -1 (unlimited).

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.
PostgreSQL Create Database

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:
PostgreSQL Create Database

Step 3

After that, enter the below command to create a database


PostgreSQL Create Database

Step 4

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


PostgreSQL Create Database

Step 5

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


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





Latest Courses