How to Create a Database in PostgreSQL [CREATE DATABASE, createdb]

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.

#Prerequisites

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

The CREATE DATABASE and createdb commands are two ways to create a database in PostgreSQL.

#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.

\conninfo

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.

\l

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 cherrydb.

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.

\l

#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.

\q

On the bash shell, use the following syntax to create a database.

createdb database_name

For example, to create a database called sampledb, run the command:

createdb sampledb

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 CREATE DATABASE statement.
• TEMPLATE - Specifies the template database from which the database will be created. When not explicitly specified, PostgreSQL uses the template1 database 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 BY clause when using the SELECT statement.
• 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.

For a more comprehensive look at database parameters, check out the CREATE DATABASE and createdb manuals.

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.

Consider the CREATE DATABASE SQL syntax shown.

CREATE DATABASE testdb
   OWNER = cherry
   ENCODING = 'UTF8'
   TEMPLATE template0;

In the above example, we have created the testdb database owned by a database user called cherry with the encoding UTF8 using the template0 template.

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 en_US.utf8 locale.

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.

#Conclusion

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.