PostgreSQL CREATE DATABASE

 PostgreSQL CREATE DATABASE

Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE DATABASE statement to create new databases in the PostgreSQL database server.

Introduction to PostgreSQL CREATE DATABASE statement

The CREATE DATABASE the statement allows you to create a new PostgreSQL database.

The following shows the syntax of the CREATE DATABASE statement:

CREATE DATABASE database_name
WITH
   [OWNER =  role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false ]

To execute the CREATE DATABASE a statement you need to have a superuser role or a special CREATEDB privilege.

To create a new database:

• First, specify the name of the new database after the CREATE DATABASE keywords. The database name must be unique in the PostgreSQL database server. If you try to create a database whose name already exists, PostgreSQL will issue an error.
• Then, specify one or more parameters for the new database.

Parameters

OWNER

Assign a role that will be the owner of the database. If you omit the OWNER option, the owner of the database is the role that you use to execute the CREATE DATABASE statement.

TEMPLATE

Specify the template database from which the new database is created. By default, PostgreSQL uses the template1 database as the template database if you don’t explicitly specify the template database.

ENCODING

Determine the character set encoding in the new database.

LC_COLLATE

Specify the collation order (LC_COLLATE) that the new database will use. This parameter affects the sort order of string in the queries that contain the ORDER BY clause. It defaults to  LC_COLLATE the template database.

LC_CTYPE

Specify the character classification that the new database will use. It affects the classification of characters e.g., lower, upper, and digit. It defaults to the LC_CTYPE of the template database

TABLESPACE

Specify the tablespace name for the new database. The default is the tablespace of the template database.

CONNECTION LIMIT

Specify the maximum concurrent connections to the new database. The default is -1 i.e., unlimited. This parameter is useful in the shared hosting environments where you can configure the maximum concurrent connections for a particular database.

ALLOW_CONNECTIONS

The allow_connections the parameter is a boolean value. If it is false, you cannot connect to the database.

TABLESPACE

Specify the tablespace that the new database will use. It defaults to the tablespace of the template database.

IS_TEMPLATE

If the IS_TEMPLATE is true, any user with the CREATEDB privilege can clone it. If false, only superusers or the database owner can clone it.

PostgreSQL CREATE DATABASE examples

1) Create a database with default parameters

First, log in to PostgreSQL using any client tool.

Second, execute the following statement to a new database with default parameters:

CREATE DATABASE sales;

PostgreSQL created a new database named sales that has default parameters from the default template database (template1).

Third, if you use the psql client tool, you can view all the databases in the current PostgreSQL database server using the \l command:

\l

2) Create a database with some parameters

The following example uses the CREATE DATABASE statement to create a database named hr with some parameters:

CREATE DATABASE hr 
WITH 
   ENCODING = 'UTF8'
   OWNER = hr
   CONNECTION LIMIT = 100;

In this example, we created the hr database with the encoding UTF8, the owner is hr and the number of concurrent connections to the database is 100.

3) Creating a new database using pgAdmin

The pgAdmin tool provides you with an intuitive interface for creating a new database.

First, log in to the PostgreSQL database server using pgAdmin.

Second, right-click the Databases node and select Create > Database… menu item

It will show a dialog for you to enter detailed information on the new database.

Third, enter the name of the database and select an owner in the general tab.

In this example, we entered sampledb the name of the new database and postgres as the owner.

Fourth, select the Definition tab to set the properties for the database:

In the Definition tab, you can select the encoding, choose a template, tablespace, collation, character type, and connection limit.

The Security tab allows you to define security labels and assign privileges. The Privileges tab allows you to assign privileges to a role.

Fifth, click the SQL tab to view the generated SQL statement that will execute.

Finally, click the Save button to create the sampledb database. You will see them sampledb listed on the database list:

Summary

• Use the CREATE DATABASE statement to create a new database.