August 1, 2023
Summary: In this tutorial, you will learn how to use the PostgreSQL CREATE DATABASE statement to create new databases in the PostgreSQL database server.
Table of Contents
Introduction to PostgreSQL CREATE DATABASE statement
The CREATE DATABASE
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
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 the LC_COLLATE
of the template database.
LC_CTYPE
Specify the character classification that the new database will use. It affects the classification of character 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
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 the 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
as 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 the sampledb
listed on the database list:
Summary
- Use the
CREATE DATABASE
statement to create a new database.