How to Create a Database in PostgreSQL

May 26, 2021

Introduction

PostgreSQL is an open-source, advanced database management system supporting relational (SQL) and non-relational (JSON) querying.

In PostgreSQL, the emphasis is mainly on extensibility and SQL compliance while maintaining ACID properties (Atomicity, Consistency, Isolation, Durability) in transactions.

In this tutorial, you will learn how to create a database in PostgreSQL using three different methods.

How to create a database in PostgreSQL.

Prerequisites

  • PostgreSQL installed and set up
  • Administrator privileges

Create a Database in PostgreSQL via pgAdmin

To create a database using pgAdmin, follow these steps:

Step 1: Open pgAdmin and enter your password to connect to the database server.

Connecting to the server in PostgreSQL.

Step 2: In the browser section, expand the Servers and then PostgreSQL items. Right-click the Databases item. Click Create and Database…

Create a database using pgAdmin.

Step 3: A new window pops up where you need to specify the database name, add a comment if necessary and click Save.

Enter database details and create a database in pgAdmin.

The database appears in the Databases object tree.

The right section of the pgAdmin window contains tabs that display database statistics, SQL commands used to create the database, any dependencies, etc.

Note: Pay attention to data type when inserting data into the database. Read our article to learn the different data types in MySQL.

Create a Database in PostgreSQL via CREATE DATABASE

Another method to create a PostrgreSQL database is to use the CREATE DATABASE command.

Follow these steps to create a database:

Step 1: Open the SQL Shell (psql) app.

Using SQL Shell to create a database in PostgreSQL.

Step 2: Connect to the DB server by pressing ENTER four times. Type in your master password if asked. If you didn't set up a password, press ENTER again to connect.

Connect to the server using the SQL Shell.

Step 3: Use the following syntax to create a new database:

CREATE DATABASE [database_name]

In place of [database_name], enter a name for your database. Make sure to enter a unique name because using an existing database name results in an error.

For example:

An example of the CREATE DATABASE command.

Creating a database without specifying any parameters takes the parameters from the default template database. See the available parameters in the next section.

Step 4: Use the following command to list all the databases in PostgreSQL:

\l
List all existing databases on the PostgreSQL server.

The output shows a list of available databases and their characteristics.

CREATE DATABASE Parameters

The available parameters for creating a database are:

  • [OWNER = role_name]

The OWNER parameter assigns the database owner role. Omitting the OWNER parameter means that the database owner is the role used to execute the CREATE DATABASE statement.

  • [TEMPLATE = template]

The TEMPLATE parameter allows you to specify the template database from which to create the new database. Omitting the TEMPLATE parameter sets template1 as the default template database.

  • [ENCODING = encoding]

The ENCODING parameter determines the character set encoding in the new database.

  • [LC_COLLATE = collate]

The LC_COLLATE parameter specifies the collation order of the new database. This parameter controls the string sort order in the ORDER BY clause. The effect is visible when using a locale that contains special characters.

Omitting the LC_COLLATE parameter takes the settings from the template database.

  • [LC_CTYPE = ctype]

The LC_CTYPE parameter specifies the character classification used in the new database. Character classification includes lower, upper case, and digits. Omitting the LC_CTYPE parameter takes the default settings from the template database.

  • [TABLESPACE = tablespace_name]

Use the TABLESPACE parameter to specify the tablespace name for the new database. Omitting the TABLESPACE parameter takes the tablespace name of the template database.

  • [ALLOW_CONNECTIONS = true | false]

The ALLOW_CONNECTIONS parameter can be TRUE or FALSE. Specifying the value as FALSE prevents you from connecting to the database.

  • [CONNECTION LIMIT = max_concurrent_connections]

The CONNECTION LIMIT parameter lets you to set the maximum simultaneous connections to a PostgreSQL database. The default value is -1, which means unlimited connections.

  • [IS_TEMPLATE = true | false ]

Set the IS_TEMPLATE parameter to TRUE or FALSE. Setting IS_TEMPLATE to TRUE allows any user with the CREATEDB privilege to clone the database. Otherwise, only superusers or the database owner can clone the database.

To create a database with parameters, add the keyword WITH after the CREATE DATABASE statement and then list the parameters you want.

For example:

Create a database with parameters in PostgreSQL.

This example shows how to set a new database to use the UTF-8 character encoding and to support a maximum of 200 concurrent connections.

Create a Database in PostgreSQL via createdb Command

The createdb command is the third method for creating a database in PostgreSQL. The only difference between the createdb and CREATE DATABASE command is that users run createdb directly from the command line and add a comment into the database, all at once.

To create a database using the createdb command, use the following syntax:

createdb [argument] [database_name [comment]]

The parameters are discussed in the following section.

Note: createdb internally runs CREATE DATABASE from psql while connected to the template1 database. The user creating the database is the only DBA and the only one who can drop the database, other than the postgres superuser.

createdb Parameters

The createdb syntax parameters are:

ParameterDescription
[argument]Command-line arguments that createdb accepts. Discussed in the next section.
[database_name]Set the database name in place of the database_name parameter.
[comment]Optional comment to be associated with the new database.

createdb Command Line Arguments

The available createdb arguments are:

ArgumentDescription
-DSpecifies the tablespace name for the new database.
-eShows the commands that createdb sends to the server.
-ESpecifies which character encoding to use in the database.
-lSpecifies which locale to use in the database.
-TSpecifies which database to use as a template for the new database.
--helpShow help page about the createdb command line arguments.
-hDisplays the hostname of the machine running the server.
-pSets the TCP port or the local Unix domain socket file extension which the server uses to listen for connections.
-USpecifies which username to use to connect.
-wInstructs createdb never to issue a password prompt.
-WInstructs createdb to issue a password prompt before connecting to a database.

For example:

Create a database directly from the CLI in Windows.

Here, we created a database called mydatabase using the default admin user postgres. We used the phoenixnap database as a template and instructed the program not to ask for a password.

createdb Command Common Errors

There are some common errors users may encounter when using the createdb command.

See the createdb error list below:

ErrorDescription
createdb command not found.Occurs when PostgreSQL was not installed properly. Run createdb from the PostgreSQL installation path or add the psql.exe path to the system variables section in PC advanced settings.
No such file or directory
Is the server running locally and accepting
connections on Unix domain socket ...?
Happens when the PostgreSQL server wasn't properly started or is not currently running.
Fatal: role "username" does not exist.Occurs when users run initdb with a role without superuser privileges.
To fix the error, create a new user with the --superuser option or login to the default admin role, postgres.
Database creation failed: ERROR: permission denied to create database.Appears when trying to create a database with an account that doesn't have the necessary permissions. To fix the error, grant superuser permissions to the role in question.

Conclusion

Now you know how to create a database in PostgreSQL using three different methods. If you prefer a GUI environment, use pgAdmin, or use the CLI or SQL Shell if you prefer running SQL commands.

If you are interested to learn more about PostgreSQL, make sure to check How to install PostgreSQL Workbench, How to Export PostgreSQL Table to CSV, How to Check PostgreSQL Version or How to Download and Install PostgreSQL on Windows.

And for information about different built-in data types available in PostgreSQL, you might find helpful our article PostgreSQL data types.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
How to Check Your PostgreSQL Version
April 21, 2020

Use this guide to check your PostgreSQL version with a few straightforward commands. You can retrieve the PostgresSQL version directly from your command line or use an effective SQL statement within the PostgreSQL shell.
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps. Use SQL Workbench to migrate data between your...
Read more
How to Deploy PostgreSQL on Docker Container
January 16, 2020

PostgreSQL is the third most popular Docker image used for deploying containers. Run PostgreSQL on Docker by pulling the Postgres image from Docker's official repository and set up the database service for your application...
Read more
PostgreSQL Vs MySQL: A Detailed Comparison
January 9, 2020

Explore the differences between the two most widely used database management systems. PostgreSQL and MySQL are both excellent database solutions, and well suited for most workloads. However, small differences can result in less...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.