creates a new
To create a database, you must be a superuser or have the special
CREATE USER (CREATE_USER(7)).
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 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 added to
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.
DROP DATABASE (DROP_DATABASE(7))
to remove a database.
is a wrapper program around this command, provided for convenience.
Database-level configuration parameters (set via
ALTER DATABASE (ALTER_DATABASE(7))) are not copied from the template database.
Although it is possible to copy a database other than
by specifying its name as the template, this is not (yet) intended as a general-purpose
facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied.
will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until
Section 21.3, Template Databases, in the documentation
for more information.
The character set encoding specified for the new database must be compatible with the chosen locale settings (LC_COLLATE
LC_CTYPE). If the locale is
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 locale.)
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
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 affected by
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.
option is only enforced approximately; if two new sessions start at about the same time when just one connection
remains for the database, it is possible that both will fail. Also, the limit is not enforced against superusers.
To create a new database:
CREATE DATABASE lusiadas;
To create a database
owned by user
with a default tablespace of
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
To create a database
which supports the ISO-8859-1 character set:
CREATE DATABASE music ENCODING LATIN1 TEMPLATE template0;
In this example, the
clause would only be required if
template1s encoding is not ISO-8859-1. Note that changing encoding might require selecting new
settings as well.