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:
In the above syntax, we have the following parameters:
| Parameters | Description |
|---|---|
| db_name | We 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_name | It is used to describe the role name for the user who will have the new database, and by default, it is postgres. |
| Template | While creating the new database, we will require database template name. |
| Encoding | It is used to describe the character set encoding for the new database, and by default, it is UTF8. |
| Collate | It 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. |
| Ctype | This parameter is used to display the character classification for the new database. |
| tablespace_name | It is used to define the tablespace name for the new database, and by default, it is the template database’s tablespace. |
| max_concurrent_connection | This 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.
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
Step 4
To get a list of all databases created earlier, we will enter the below command:
Step 5
To connect to a database, we will enter the below command:
- As we can see in the above screenshot that we are connected to database tutoraspire through the command prompt.
- And we can also execute various commands such as Trigger, Create Table, and so on.