CREATE DATABASE — create a new database
name[ [ WITH ] [ OWNER [=]
user_name] [ TEMPLATE [=]
template] [ ENCODING [=]
encoding] [ LC_COLLATE [=]
lc_collate] [ LC_CTYPE [=]
lc_ctype] [ TABLESPACE [=]
tablespace_name] [ ALLOW_CONNECTIONS [=]
allowconn] [ CONNECTION LIMIT [=]
connlimit] [ IS_TEMPLATE [=]
CREATE DATABASE creates a new
To create a database, you must be a superuser or have the
CREATEDB privilege. See
By default, the new database will be created by cloning the
standard system database
different template can be specified by writing
TEMPLATE . In particular, by
TEMPLATE template0, you can
create a virgin database 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
The name of a database to create.
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.
The name of the template from which to create the new database,
DEFAULT to use the default template
Character set encoding to use in the new database. Specify a
string constant (e.g.,
or an integer encoding number, or
DEFAULT to use the default encoding (namely, the
encoding of the template database). The character sets supported by
the PostgreSQL server are
described in Section 23.3.1.
See below for additional restrictions.
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. See below for additional
Character classification (
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. See below for
The name of the tablespace that will be associated with the new
DEFAULT to use the
template database's tablespace. This tablespace will be the default
tablespace used for objects created in this database. See CREATE
TABLESPACE for more information.
If false then no one can connect to this database. The default
is true, allowing connections (except as restricted by other
mechanisms, such as
How many concurrent connections can be made to this database. -1 (the default) means no limit.
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
Optional parameters can be written in any order, not only the order illustrated above.
CREATE DATABASE cannot be executed
inside a transaction block.
Errors along the line of “could not initialize database directory” are most likely related to insufficient permissions on the data directory, a full disk, or other file system problems.
Use DROP DATABASE to remove a database.
The program createdb is a wrapper program around this command, provided for convenience.
Database-level configuration parameters (set via ALTER DATABASE) are not copied from the template database.
Although it is possible to copy a database other than
template1 by specifying its name as
the template, this is not (yet) intended as a general-purpose
DATABASE” facility. The principal limitation
is that no other sessions can be connected to the template database
while it is being copied.
DATABASE will fail if any other connection exists when it
starts; otherwise, new connections to the template database are
locked out until
completes. See Section 22.3 for
The character set encoding specified for the new database must
be compatible with the chosen locale settings (
LC_CTYPE). If the locale is
C (or equivalently
POSIX), then all encodings are allowed, but for
other locale settings there is only one encoding that will work
properly. (On Windows, however, UTF-8 encoding can be used with any
CREATE DATABASE will allow
superusers to specify
encoding regardless of the locale settings, but this choice is
deprecated and may result in misbehavior of character-string
functions if data that is not encoding-compatible with the locale
is stored in the database.
The encoding and locale settings must match those of the
template database, except when
template0 is used as template. This is because
other databases might contain data that does not match the
specified encoding, or might contain indexes whose sort ordering is
LC_CTYPE. Copying such data would
result in a database that is corrupt according to the new settings.
template0, however, is known to not
contain any data or indexes that would be affected.
CONNECTION LIMIT option is only
enforced approximately; if two new sessions start at about the same
time when just one connection “slot” remains for the database, it is
possible that both will fail. Also, the limit is not enforced
against superusers or background worker processes.
To create a new database:
CREATE DATABASE lusiadas;
To create a database
sales owned by
salesapp with a default
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
To create a database
music with a
CREATE DATABASE music LC_COLLATE 'sv_SE.utf8' LC_CTYPE 'sv_SE.utf8' TEMPLATE template0;
In this example, the
template0 clause is required if the specified locale is
different from the one in
(If it is not, then specifying the locale explicitly is
To create a database
music2 with a
different locale and a different character set encoding:
CREATE DATABASE music2 LC_COLLATE 'sv_SE.iso885915' LC_CTYPE 'sv_SE.iso885915' ENCODING LATIN9 TEMPLATE template0;
The specified locale and encoding settings must match, or an error will be reported.
Note that locale names are specific to the operating system, so that the above commands might not work in the same way everywhere.
There is no
statement in the SQL standard. Databases are equivalent to
catalogs, whose creation is implementation-defined.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.