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