PostgreSQL CREATE DATABASE with Example

The syntax to create a database in PostgreSQL is

CREATE DATABASE databasename

Let’s see how to create a database in the command line and the GUI

PSQL Create Database Command Line (SQL Shell)

Step 1) Open the SQL Shell

Step 2) Press enter five times to connect to the DB

Step 3) Enter the command

CREATE DATABASE guru99;

Step 4) Enter command \l to get a list of all databases

Step 5) To connect to a Database use the command

\c guru99

Prompt changes to guru99 which signifies that we are connected to database guru99 and can perform operations like create table, trigger, execute SQL on it.

PostgreSQL Create Database using pgAdmin

Step 1) In the Object Tree, right click and select create a database

Step 2) In the pop-up,

Enter Database Name
Comment if any database – optional
Click Save

Step 3) DB is created and shown in the Object tree.

Step 4) The right pane gives you the SQL used to create the Database.

Complete syntax to create a database

Here is complete Syntax to create a DB in PostgreSQL

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

Option	Description
db_name	Use this option to specify the name of the new database that you want to create. Although, you need to make sure that the database must be unique because If you attempt to create a new database with the same name as an existing database, PostgreSQL will display an error.
role_name	Use this parameter to define the the role name for the user who will own the new database. Default is postgres
Template	You can specify database template name from which you want to creates the new database.
Encoding	This parameter allows specifying character set encoding for the new database. Default is UTF8
Collate	The collation parameter specifies the sort order of strings which affect the result of the ORDER BY clause while using a SELECT statement.
Ctype	It specifies the character classification for the new database. It affects the categorization, e.g., digit, lower and upper.
tablespace_name	Using this option you can specify the tablespace name for the new database. The default is the template database’s tablespace.
max_concurrent_connection	Use this option to specify the maximum concurrent connections to the new database. The default is -1, i.e., unlimited.

Common Errors while using the createdb command

Error	Description
createdb command not found.	This kind of error may occur when PostgreSQL is not installed correctly. At that time, you need need to run createdb command from your PostgreSQL installation path.
No such file in the server is running locally and accepting connections on Unix domain socket.	This error occurs when PostgreSQL Server is not started properly, or it was not started where the createdb command wants it to start.
FATAL role “usr name” does not exist	This error may occur if the PostgreSQL user account is created which are different from system user accounts.
Permission denied to create a database	If the PostgreSQL account is created does not have permission to create a database In this case, you need to grant permission to the associated users to access create command.

Summary

You can create a database using the Command Line (SQL Shell) with the command ” CREATE DATABASE databasename
You can also create a database using pgAdmin by following this step: In the Object Tree > right click on Database > Select create a database
One of the common errors encountered when create database command fails is that the server is not initialized correctly. Rebooting the server solves the issue

LOGIN

PostgreSQL CREATE DATABASE with Example