PostgreSQL Create Database with Example

February 1, 2021

In this article, we will discuss about PostgreSQL Create Database statement to create a new database in PostgreSQL database cluster. PostgreSQL server consists of multiple database which is called a database cluster. In other words, a database cluster is a collection of multiple databases that is managed by a single instance of a running database server.

PostgreSQL create database concepts and example.

When PostgreSQL cluster is initialized, template0, template1 and postgres databases are created. The postgres database is the default database which is created using template1 database. If you do not specify any database at connection time, you will be connected to the postgres database. So if you type psql without database name, you will be connected to the default postgres database.

Whenever you create a new database within the cluster, template1 is essentially cloned. This means that any changes you make in template1 are propagated to all subsequently created databases. Because of this, avoid creating objects in template1 unless you want them propagated to every newly created database.

Postgres list databases – To show Existing databases.

To determine the set of existing databases, examine the pg_database system catalog. Note that you must execute this command while logged into the PostgreSQL user account. The psql program’s \l meta-command and -l command-line option are also used for listing the existing databases. For example:

postgres=# SELECT datname FROM pg_database;
datname
postgres
template1
template0
(3 rows)
PostgreSQL list database is used to show existing database.

Syntax to Create PostgreSQL Database with Owner and other parameters 

CREATE DATABASE database_name
         WITH OWNER = postgres 
         TEMPLATE = template_name
         ENCODING = 'encoding'
         LC_COLLATE = 'lc_collate'
         LC_CTYPE = 'lc_ctype'
         TABLESPACE = TABLESPACE_NAME
         CONNECTION LIMIT = max_concurrent_connection;

PostgreSQL- How to Create Database using Command Line

In order to create a database, the PostgreSQL server must be up and running. The syntax to create database is:

CREATE DATABASE database_name;

There are many options you can use while creating a database.

PSQL – Postgres create database command line Steps

STEP 1: Connect to superuser account which is postgres in Linux and Windows.

[root@orahow db_home]# su - postgres
bash-4.1$ id
uid=54323(postgres) gid=54323(postgres) groups=54323(postgres)

STEP 2: Connect to default postgres database using psql.

bash-4.1$ psql
psql (12.1)
Type "help" for help.
postgres=# SELECT current_database();
current_database
postgres
(1 row)

STEP 3: Create a database.

Syntax to create PostgreSQL database in Ubuntu, Windows and Linux are same. So you can use this steps in any of your environment.

postgres=# CREATE DATABASE orahow;
CREATE DATABASE

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
orahow | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres

STEP 4: To connect PostgreSQL database using command line.

postgres=# \c orahow
 You are now connected to database "orahow" as user "postgres".
 orahow=#

STEP 5: Create user and gran access to new database.

postgres=# create user dev_user with encrypted password 'dev_user';
 CREATE ROLE
 postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to dev_user;
 GRANT

STEP 6: To connect to database using new user.

bash-4.1$ psql -h localhost -p 5432 -U dev_user testdb
testdb=> SELECT DISTINCT usename
 testdb-> FROM pg_stat_activity;
  usename
 postgres
  dev_user
 (3 rows)

You can also provide more specific details while creating database like characterset encoding, with owner etc.

Examples:

postgres=#  CREATE DATABASE testdb
                      WITH OWNER postgres
                      ENCODING 'UTF8'
                      LC_COLLATE = 'en_US.UTF-8'
                      LC_CTYPE = 'en_US.UTF-8';

Summary:

  • Use CREATE DATABASE command to create a new database.
  • You can also create more specific custom database using templates, character set, encoding, owner etc.
  • You can also create dedicated PostgreSQL user and grant all permission to connect to new database.

Must Read: