CREATE DATABASE - Creates a new database
SYNOPSIS
CREATE DATABASE name
[ WITH [ LOCATION = 'dbpath' ]
[ TEMPLATE = template ]
[ ENCODING = encoding ] ]
INPUTS
name The name of a database to create.
dbpath An alternate filesystem location in which to store
the new database, specified as a string literal; or
DEFAULT to use the default location.
template
Name of template from which to create the new
database, or DEFAULT to use the default template
(template1).
encoding
Multibyte encoding method to use in the new
database. Specify a string literal name (e.g.,
'SQL_ASCII'), or an integer encoding number, or
DEFAULT to use the default encoding.
OUTPUTS
CREATE DATABASE
Message returned if the command completes success
fully.
ERROR: user 'username' is not allowed to create/drop
databases
You must have the special CREATEDB privilege to
create databases. See CREATE USER [cre
ate_user(l)].
ERROR: createdb: database "name" already exists
This occurs if a database with the name specified
already exists.
ERROR: database path may not contain single quotes
The database location dbpath cannot contain single
quotes. This is required so that the shell commands
that create the database directory can execute
safely.
ERROR: CREATE DATABASE: may not be called in a transaction
block
If you have an explicit transaction block in
finish the transaction first.
ERROR: Unable to create database directory 'path'.
ERROR: Could not initialize database directory.
These are most likely related to insufficient per
missions on the data directory, a full disk, or
other file system problems. The user under which
the database server is running must have access to
the location.
DESCRIPTION
CREATE DATABASE creates a new Postgres database. The cre
ator becomes the owner of the new database.
An alternate location can be specified in order to, for
example, store the database on a different disk. The path
must have been prepared with the initlocation [initloca
tion(1)] command.
If the path name does not contain a slash, it is inter
preted as an environment variable name, which must be
known to the server process. This way the database admin
istrator can exercise control over locations in which
databases can be created. (A customary choice is, e.g.,
'PGDATA2'.) If the server is compiled with ALLOW_ABSO
LUTE_DBPATHS (not so by default), absolute path names, as
identified by a leading slash (e.g.,
'/usr/local/pgsql/data'), are allowed as well.
By default, the new database will be created by cloning
the standard system database template1. A different tem
plate can be specified by writing TEMPLATE = name. In par
ticular, by writing TEMPLATE = template0, you can create a
virgin database containing only the standard objects pre
defined by your version of Postgres. This is useful if you
wish to avoid copying any installation-local objects that
may have been added to template1.
The optional encoding parameter allows selection of the
database encoding, if your server was compiled with multi
byte encoding support. When not specified, it defaults to
the encoding used by the selected template database.
Optional parameters can be written in any order, not only
the order illustrated above.
NOTES
CREATE DATABASE is a Postgres language extension.
Use DROP DATABASE [drop_database(l)] to remove a database.
script wrapper around this command, provided for conve
nience.
There are security and data integrity issues involved with
using alternate database locations specified with absolute
path names, and by default only an environment variable
known to the backend may be specified for an alternate
location. See the Administrator's Guide for more informa
tion.
Although it is possible to copy a database other than tem
plate1 by specifying its name as the template, this is not
(yet) intended as a general-purpose COPY DATABASE facil
ity. In particular, it is essential that the source
database be idle (no data-altering transactions in
progress) for the duration of the copying operation. CRE
ATE DATABASE will check that no backend processes (other
than itself) are connected to the source database at the
start of the operation, but this does not guarantee that
changes cannot be made while the copy proceeds. Therefore,
we recommend that databases used as templates be treated
as read-only.
Two useful flags exist in pg_database for each database:
datistemplate and datallowconn. datistemplate may be set
to indicate that a database is intended as a template for
CREATE DATABASE. If this flag is set, the database may be
cloned by any user with CREATEDB privileges; if it is not
set, only superusers and the owner of the database may
clone it. If datallowconn is false, then no new connec
tions to that database will be allowed (but existing ses
sions are not killed simply by setting the flag false).
The template0 database is normally marked this way to pre
vent modification of it.
USAGE
To create a new database:
olly=> create database lusiadas;
To create a new database in an alternate area ~/pri
vate_db:
$ mkdir private_db
$ initlocation ~/private_db
Creating Postgres database system directory /home/olly/private_db/base
$ psql olly
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
olly=> CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';
CREATE DATABASE
COMPATIBILITY
SQL92
There is no CREATE DATABASE statement in SQL92. Databases
are equivalent to catalogs whose creation is implementa
tion-defined.
Man(1) output converted with
man2html