Creating and Managing Databases

A PostgreSQL cluster can have many databases, in addition to default databases created as part of the cluster creation.

A new database inside the PostgreSQL cluster can be created in many ways. The basic methods are running SQL command CREATE DATABASE on psql prompt or running PostgreSQL executable createdb on operating system prompt.

To create a database, you must be a superuser or have the special CREATEDB privilege.

In PostgreSQL, SQL commands/statements are case-insensitive. So, SQL create database is the same as CREATE DATABASE.

Similarly, the database and object names are also case-insensitive in general. So, testdb is the same as TESTDB. But, when put inside double-quotes, then these become case-sensitive. So, testdb and "TESTDB" are different. Without double-quotes, the database or object name is considered to be lower-case from the PostgreSQL perspective irrespective of the case in which it is written. If a database or object is created with a double-quoted name, then going forward, it can be used, accessed, or dropped with double-quoted name only, in general. For example, testdb and TESTDB both will point to the database testdb but "TESTDB" will point to the database “TESTDB“.

Creating databases using SQL command CREATE DATABASE

Syntax of CREATE DATABASE is as below. Full details are available here.

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ] 

Important parameters for CREATE DATABASE are:

• NAME – the name of the database to be created. This is the only mandatory option.
• OWNER – the role name who will own the database. It defaults to the user running the create command.
• TEMPLATE – the database from which new database will be copied to create. It defaults to template1.
• ENCODING – Character set encoding to use in the new database. It defaults to the encoding of the template database.
• CONNECTION LIMIT – Maximum concurrent connections which can be made to this database. It defaults to -1 which means no limit.
• IS_TEMPLATE – Specifies if the new database can be used as a template for another database. It defaults to false.

Creating a simple database

Connect to psql prompt of the cluster and run the below command to create a database named testdb. When no additional options are given, the new database is created as a copy from the default template database template1. The content of this default template can be modified as needed, and any new databases created afterwards will have the same starting content as in this template database. There is another standard template database template0 but the content of this template database can not be modified.

$ psql

# CREATE DATABASE testdb;

# \l

# \l testdb

$ psql
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
Type "help" for help.

postgres=# create database testdb;
2021-08-08 10:38:27.760 IST [1451] WARNING:  could not flush dirty data: Function not implemented
WARNING:  could not flush dirty data: Function not implemented
CREATE DATABASE
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(4 rows)

postgres=#

Let’s create another with double-quoted name “testdb” and then another one with “TESTDB”.

# create database "testdb";
# create database "TESTDB";
# \l

postgres=# create database "testdb";
2021-08-08 10:42:58.276 IST [1451] ERROR:  database "testdb" already exists
2021-08-08 10:42:58.276 IST [1451] STATEMENT:  create database "testdb";
ERROR:  database "testdb" already exists
postgres=#
postgres=# create database "TESTDB";
CREATE DATABASE
postgres=#
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 TESTDB    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(5 rows)

postgres=#

Let’s create another database specifying the template name and a few more options.

# CREATE DATABASE appdb TEMPLATE template0 OWNER = postgres CONNECTION LIMIT = 150;
# \l
# \l appdb

postgres=# CREATE DATABASE appdb TEMPLATE template0 OWNER = postgres CONNECTION LIMIT = 150;
2021-08-08 10:53:49.601 IST [1490] WARNING:  could not flush dirty data: Function not implemented
WARNING:  could not flush dirty data: Function not implemented
CREATE DATABASE
postgres=#
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 TESTDB    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 appdb     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(6 rows)

postgres=# \l appdb
                          List of databases
 Name  |  Owner   | Encoding | Collate |  Ctype  | Access privileges
-------+----------+----------+---------+---------+-------------------
 appdb | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(1 row)

postgres=#

Now, let’s create one more database with IS_TEMPLATE = true so that it can be used as a template for another database creation. Also, we will use WITH ENCODING parameter to set a particular character set for the new database. We have to also specify locale parameters if the given encooding does not match the default locale.

As seen below, create database fails due to a mismatch of encoding and locale.

# CREATE DATABASE hrdb WITH ENCODING 'LATIN1' CONNECTION LIMIT = 200;

postgres=# CREATE DATABASE hrdb WITH ENCODING 'LATIN1' CONNECTION LIMIT = 200;
2021-08-11 10:42:48.401 IST [181] ERROR:  encoding "LATIN1" does not match locale "C.UTF-8"
2021-08-11 10:42:48.401 IST [181] DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".
2021-08-11 10:42:48.401 IST [181] STATEMENT:  CREATE DATABASE hrdb WITH ENCODING 'LATIN1' CONNECTION LIMIT = 200;
ERROR:  encoding "LATIN1" does not match locale "C.UTF-8"
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".
postgres=#

Let’s create a database with a given encoding and matching locale. Current locales can be displayed using the command locale. Creating a database also fails if the specified encoding for the new database doesn’t match with the encoding of the default template database template1. To simulate that case, meanwhile, we will first change the locale of the system. Using the command locale -a, we can see available locales, and using the command locale, we can see the current locale. In the current system, the current locale is C.UTF-8 and all the databases created so far would have the same locale.

$ locale
$ locale -a
$ psql -c "\l"

$ locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=
postgres@MKM-HP-ENVY:~$
postgres@MKM-HP-ENVY:~$ locale -a
C
C.UTF-8
POSIX
en_US.utf8
$
$ psql -c "\l"
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 db1       | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

$

# CREATE DATABASE hrdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' CONNECTION LIMIT = 200;

postgres=# CREATE DATABASE hrdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' CONNECTION LIMIT = 200;
2021-08-11 12:04:28.271 IST [134] ERROR:  new collation (en_US.utf8) is incompatible with the collation of the template database (C.UTF-8)
2021-08-11 12:04:28.271 IST [134] HINT:  Use the same collation as in the template database, or use template0 as template.
2021-08-11 12:04:28.271 IST [134] STATEMENT:  CREATE DATABASE hrdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' CONNECTION LIMIT = 200;
ERROR:  new collation (en_US.utf8) is incompatible with the collation of the template database (C.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.
postgres=#

The above creation failed as the default template database template1 has locale C.UTF-8 while we are trying to create a database with locale en_US.utf8.

So, we have to switch the template parameter to the standard template database template0 which allows the creation of the database with a non-default locale. As seen below, the new database hrdb has locale en_US.utf8.

# CREATE DATABASE hrdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' TEMPLATE = template0 CONNECTION LIMIT = 200 IS_TEMPLATE = TRUE;
# \l

postgres=# CREATE DATABASE hrdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' TEMPLATE = template0 CONNECTION LIMIT = 200 IS_TEMPLATE = TRUE;
CREATE DATABASE
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 hrdb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | C.UTF-8    | C.UTF-8    |
 template0 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=#

Now, let's create another database using above database as the template database.

# CREATE DATABASE accountdb TEMPLATE = hrdb;

postgres=# CREATE DATABASE accountdb TEMPLATE = hrdb;
CREATE DATABASE
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 accountdb | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 hrdb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | C.UTF-8    | C.UTF-8    |
 template0 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(5 rows)

postgres=#

Creating databases using PostgreSQL OS command createdb

One can also create the database using PostgreSQL operating system executable createdb specifying the template name and a few more options. To see all available options with createdb, run this command with --help options.

$ createdb --help

$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
$

Important parameters for createdb are:

• -O – the role name who will own the database. It defaults to the user running the create command.
• -T – the database from which new database will be copied to create. It defaults to template1.
• -E – Character set encoding to use in the new database. It defaults to the encoding of the template database.

Let’s create a database with this command.

$ createdb findb -T template0 -O postgres;
$ psql -c "\l"

$ createdb findb -T template0 -O postgres;
2021-08-11 12:22:19.669 IST [153] WARNING:  could not flush dirty data: Function not implemented
WARNING:  could not flush dirty data: Function not implemented
$
$ psql -c "\l"
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 accountdb | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 findb     | postgres | UTF8     | C.UTF-8    | C.UTF-8    |
 hrdb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | C.UTF-8    | C.UTF-8    |
 template0 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8    | C.UTF-8    | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(6 rows)

$

Altering the databases

One can alter the databases after the creation to change various options or parameters.

Syntax is as below. Full details are available here.

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

The following examples will help explain this better.

# ALTER DATABASE findb OWNER TO john;

# ALTER DATABASE findb SET enable_indexscan TO off;

# ALTER DATABASE findb WITH IS_TEMPLATE = true;

# ALTER DATABASE findb WITH ALLOW_CONNECTIONS = false;

# ALTER DATABASE findb WITH CONNECTION LIMIT 100;

# ALTER DATABASE findb SET TABLESPACE app_tbs;

# ALTER DATABASE findb SET timezone = 'UTC-5';

# ALTER DATABASE findb SET escape_string_warning = off;

# ALTER DATABASE findb RESET escape_string_warning;

# ALTER DATABASE findb RESET ALL;

Renaming the databases

One can rename a database if there are no connected users. Only the owner of the database or superuser can make this change, and also the user must have CREATEDB privilege.

# ALTER DATABASE findb RENAME TO findb2;

Dropping the databases

One can drop or remove a database if there are no connected users. This will remove all the data in the database and can not be undone. Only the owner of the database or superuser can make this change.

# DROP DATABASE findb2;