CREATE DATABASE

The CREATE DATABASE command creates a new database.

Description

To create a database, you must be a superuser or have the special CREATEDB privilege. By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

Usage

The general command view is listed below:

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 ] ]
Arguments
Parameter Description

name

The name of a database to create

user_name

The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser

template

The name of the template from which to create the new database, or DEFAULT to use the default template (template1)

encoding

Character set encoding to use in the new database. Specify a string constant (e.g., 'SQL_ASCII'), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database)

locale

This is a shortcut for setting LC_COLLATE and LC_CTYPE at once. If you specify this, you cannot specify either of those parameters

lc_collate

Collation order (LC_COLLATE) to use in the new database. This affects the sort order applied to strings, e.g., in queries with ORDER BY, as well as the order used in indexes on text columns. The default is to use the collation order of the template database

lc_ctype

Character classification (LC_CTYPE) to use in the new database. This affects the categorization of characters, e.g., lower, upper and digit. The default is to use the character classification of the template database

tablespace_name

The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database

allowconn

If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT/REVOKE CONNECT)

connlimit

How many concurrent connections can be made to this database. -1 (the default) means no limit

istemplate

If true, then this database can be cloned by any user with CREATEDB privileges. If false (the default), then only superusers or the owner of the database can clone it

Example

Create a new database:

CREATE DATABASE customers;

Create a database sales owned by user salesapp with a default tablespace of salesspace:

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

Create a database music with a different locale:

CREATE DATABASE music
    LOCALE 'sv_SE.utf8'
    TEMPLATE template0;

In this example, the TEMPLATE template0 clause is required if the specified locale is different from the one in template1. If it is not, then specifying the locale explicitly is redundant.

To create a database music2 with a different locale and a different character set encoding:

CREATE DATABASE music2
    LOCALE 'sv_SE.iso885915'
    ENCODING LATIN9
    TEMPLATE template0;

The specified locale and encoding settings must match, or an error will be reported.

Compatibility

There is no CREATE DATABASE statement in the SQL standard. Databases are equivalent to catalogs, whose creation is implementation-defined.

Found a mistake? Seleсt text and press Ctrl+Enter to report it