PostgreSQL Create Database

A database refers to structured data which is organized for easy access, management, and updates. Relational databases are one of the most common database types in the modern age where the data is organized into rows and columns. One such type of DBMS is PostgreSQL.

PostgreSQL is a free and open-source object-relational database system that uses and extends the SQL language combined with numerous features to store and scale the complicated data workloads safely.

One of the first steps in working with PostgreSQL is creating a database which serves as a structured data set where we can perform various operations like insert, update, delete, and retrieve the data.

In this tutorial, we will walk you through creating a PostgreSQL database, verifying its creation, connecting to it, and deleting it if necessary.

Requirements:
We assume that you have an installed PostgreSQL server on your machine for this tutorial. We also assume that you have permission to create new databases on the server.

Step 1: Connect to PostgreSQL Server
The first step is to connect to the PostgreSQL server using your desired client. For this post, we use the psql command-line utility.

We can run the following command to connect to the PostgreSQL server:

psql -h hostname -U username

Replace the username with your target user. Remember that any databases that we create will be owned by the currently logged-in user unless otherwise specified.

Step 2: Create a User in PostgreSQL
To create a new database in PostgreSQL, we use the CREATE DATABASE command followed by the name of the database. The command syntax is as follows:

CREATE DATABASE database_name;

It is good to keep in mind that the previous example is a simplified version of the CREATE DATABASE command. PostgreSQL allows us to specify other explicit options as dictated in the following syntax:

CREATE DATABASE database_name
[ [ WITH ] [ OWNER = role_name ]
[ TEMPLATE = template_name ]
[ ENCODING = encoding ]
[ LC_COLLATE = collate ]
[ LC_CTYPE = ctype ]
[ TABLESPACE = tablespace_name ]
[ CONNECTION LIMIT = max_connections ] ]

The given syntax supports a collection of parameters as described in the following:

CREATE DATABASE CLAUSE

The first is the CREATE DATABASE clause which tells PostgreSQL that we wish to create a new database on the server. This includes the database name which defines the database name that we wish to create. It is good to keep the name of the database as meaningful and short.

WITH CLAUSE

The WITH clause allows us to specify additional parameters for the database.

OWNER CLAUSE

Next is the OWNER clause which allows us to set the database owner in the OWNER=role_name format. Remember that the provided role_name should be an existing user or group in the cluster. If not specified, the database is owned by the user that executes the CREATE DATABASE command.

TEMPLATE

The TEMPLATE option allows us to specify a template database which is used as a blueprint for the new database. This is useful when you need to copy the structure of a given database.

ENCODING

We also have the ENCODING option which allows us to define the character encoding for that database. Some standard character encoding options include UTF8 for Unicode, LATIN1, etc. PostgreSQL uses the default encoding which is defined in the database template if missing.

LC_COLLATE

The LC_COLLATE option allows us to specify the collation that is used in the database. In addition, the LC_COLLATE defines that database’s sorting and comparison rules.

LC_TYPE

This parameter sets the character classification rules that are used in the database.

TABLESPACE

The TABLESPACE parameter allows us to specify a tablespace where the database objects are stored. To simply put it, it refers to the path or location on the file system where the database files are stored.

CONNECTION_LIMIT

Finally, the CONNECTION_LIMIT parameter allows us to set the maximum number of concurrent connections that can connect to the database.

PostgreSQL Create Database Examples

Let us cover some basic examples of creating a database in PostgreSQL.

Example 1: Using the Default Parameters
We can simply run the CREATE DATABASE command to create a database using the default parameters and pass the target database name.

Example:

CREATE DATABASE sample_db;

The given statement creates a new database called “sample_db” and uses all the default parameters that are defined in the server.

Example 2: Using the Custom Options
The following example demonstrates how we can specify the custom parameters when creating a new database in PostgreSQL:

CREATE DATABASE sample_db
WITH OWNER = postgres
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
CONNECTION LIMIT = 100;

In this example, we specify the parameters such as the database owner, target template, encoding, target collation, character type, and connection limit.

Step 3: List the Database in PostgreSQL
Once we execute the CREATE DATABASE command, confirming that the database is created successfully is a good practice. We can do this by listing the available databases using the “\l” command in the PSQL utility.

This should return all the databases including the newly created ones.

Step 4: Create the Database Using PgAdmin
You can also create a database using the graphical interface of the PgAdmin utility.

Launch the PgAdmin 4 and connect to your PostgreSQL server by providing the necessary credentials.

Expand the server group in the object tree on the left-hand side, then the server, and finally the Databases folder.

Right-click on the “Databases” folder and select “Create” from the context menu. Alternatively, click the “Create” button in the toolbar and choose “Database” from the dropdown menu.

This launches the “Create Database” dialog box. Provide the database details in the “General” tab such as:

Database name
Database owner

In the “Definition” tab, you can configure the additional options for the database if desired. These options include:

Database Template
Database Encoding
Collation
Character Type

You can grant specific privileges to different roles on the newly created database in the “Privileges” tab. By default, the owner has all the privileges.

Once you provide all the necessary details, click the “Save” button to create the database.

This should create a new database which allows you to access and perform the actions. In PgAdmin, you can view the newly created database in the “Databases” folder in the Object Tree on the left-hand pane.

Conclusion

You learned the various methods and techniques to create a new database in PostgreSQL. You learned how to create a new database with default options or how to configure the database options in the SQL statement. Finally, you discovered how to create a new database using the PgAdmin Graphical Interface.