PostgreSQL is known for its rich feature set, reliability, scalability, and strong community support network. This tutorial focuses on how to create a database in PostgreSQL using the
CREATE DATABASE SQL statement and Postgres
createdb command-line utility. You will also learn to create a database using database parameters.
What is PostgreSQL?
PostgreSQL is a widely used open-source Relational Database Management System (RDBMS) in web and mobile applications thanks to its scalability, extensibility, and other valuable features, including advanced querying capabilities and replication. Since its release in 1996, PostgreSQL has carved a niche as one of the most robust and reliable database systems in the Tech industry.
To get started and create a database in Postgres, first ensure that you have PostgreSQL installed on your server instance. Check out our comprehensive guide on how to install PostgreSQL on Ubuntu 22.04.
In addition, ensure that you have a sudo user configured on your server.
How to create a database in PostgreSQL: Two options
Option 1. Postgres CREATE DATABASE SQL statement
Let's start with the Postgres: CREATE DATABASE SQL statement. The conventional way of interacting with PostgreSQL is via the command line
CREATE DATABASE query using the SQL query language, which is the standard in most Relational Database Management Systems (RDBMS).
To access the Postgres shell prompt, run the following command. The command logs you in as the
Postgres user, which is a UNIX account user included by default during installation.
sudo -u postgres psql
You can confirm who you are logged in as by checking the connection details, as shown below.
From the output, you can see that you are logged in as a
postgres user, which, by default, is the administrative user.
PostgreSQL ships with three databases by default: postgres, template0, and template1. To verify this, issue the following query.
To create a database, run the
CREATE DATABASE query followed by the database name. In the syntax below,
dbname is the name of the database we will create.
CREATE DATABASE dbname;
A few things to note:
The database name should be unique. If a database with the same name in the database server already exists, PostgreSQL will issue an error;
To run the
CREATE DATABASE query, you must either have the superuser role or grant the CREATEDB privilege to the database user. Since we are connected to the database server as
postgres user, which is already the default superuser, no further action is required;
Run the following SQL query to create a database called
CREATE DATABASE cherrydb;
The current role automatically assumes ownership of the new database. A role is considered a "user" or "group," depending on the context. The concepts of 'users' and 'roles' have been merged in modern Postgres versions, making them almost indistinguishable. In the above example, the
postgres user assumes database ownership.
The database owner has privileges to modify and remove it, including all its objects, even when they have a different owner. Thus, the
postgres user can modify or remove the database.
Once again, you can list the existing databases to confirm the creation of the database. This time around, our newly created database is listed.
Option 2: Postgres createdb command
Another option to create a database in Postgres is the Postgres createdb command. The only difference between the
createdb command and the
CREATE DATABASE statement is that the former is run directly from the bash shell, while the latter is issued from the psql shell.
To create a database in PostgreSQL using this command, the UNIX user account should have a corresponding database user or role created. So first, log into the psql shell.
sudo -i -u postgres psql
Create a database user that corresponds to the name of the normal UNIX account using the following syntax.
CREATE USER [name] WITH PASSWORD '[password]';
In this example, we are creating a user called
cherry since we are running the bash shell as the same user.
CREATE USER cherry WITH PASSWORD 'P@ss5476';
Next, grant the user privileges to create a database with the
ALTER USER query.
ALTER USER cherry CREATEDB;
Then quit the psql shell.
On the bash shell, use the following syntax to create a database.
For example, to create a database called
sampledb, run the command:
If you run the command as the
postgres user, simply access the
Postgres account and create a database without any additional steps since
postgres is already a superuser.
Create a database using database parameters
The examples we have looked at so far demonstrate the creation of a database without specifying additional database parameters. These parameters are used to specify various database attributes. When omitted during database creation, default parameters are applied.
Let’s take a look at some of the commonly used database parameters:
OWNER - Specifies the role or database user that will own the database. When omitted, the database owner is the role you use to execute the
TEMPLATE - Specifies the template database from which the database will be created. When not explicitly specified, PostgreSQL uses the
template1database as the database template.
LC_COLLATE - This is the collation order that the database will acquire. It specifies the sort order of strings. It affects the
ORDER BYclause when using the
- LC_CTYPE - Specifies the character classification ( LC_CTYPE ) that the new database will use. It determines how characters are classified, e.g., lower and upper characters, numeric, etc.
- TABLESPACE - Specifies the tablespace name for the new database. A tablespace is a storage structure containing tables and other database objects. It organizes data in a database into logical storage groupings that point to the storage location of the data.
- CONNECTION LIMIT - This specifies the maximum concurrent connections that a database can accept. The default value is set to -1 ( Unlimited )
- ALLOW_CONNECTIONS - This is a boolean value. When the value is set to false, you cannot make any connections to the database.
Note that these options are not supported in PostgreSQL 11 and earlier versions.
Let’s now practice creating a database with some of the parameters we have looked at.
CREATE DATABASE SQL syntax shown.
CREATE DATABASE testdb
OWNER = cherry
ENCODING = 'UTF8'
In the above example, we have created the
testdb database owned by a database user called
cherry with the encoding UTF8 using the
Let’s take another example with the
createdb command. In this command, we have created a database called
exampledb owned by
cherry using the
template0 template with
UTF8 encoding and
createdb exampledb -O cherry -T template0 -E UTF8 -l en_US.utf8
BONUS TIP To find the available locales on your system, run the
locale -a command on your bash shell.
In this article, you have learned how to create a database in PostgreSQL. We have looked at creating a Postgres database using the
CREATE DATABASE SQL statement and the Postgres 'createdb' command line utility. Furthermore, we have explored various database parameters and how to create a database while specifying them.
For more information, check to the official PostgreSQL documentation to build your application.