doc/src/sgml/ref/create_database.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEDATABASE">
   7  <refmeta>
   8   <refentrytitle id="sql-createdatabase-title">CREATE DATABASE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE DATABASE</refname>
  15   <refpurpose>create a new database</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createdatabase">
  19   <primary>CREATE DATABASE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
  25     [ [ WITH ] [ OWNER [=] <replaceable class="parameter">dbowner</replaceable> ]
  26            [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
  27            [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
  28            [ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
  29            [ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
  30            [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ]
  31            [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
  32 </synopsis>
  33  </refsynopsisdiv>
  34
  35  <refsect1>
  36   <title>Description</title>
  37
  38   <para>
  39    <command>CREATE DATABASE</command> creates a new
  40    <productname>PostgreSQL</productname> database.
  41   </para>
  42
  43   <para>
  44    To create a database, you must be a superuser or have the special
  45    <literal>CREATEDB</> privilege.
  46    See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
  47   </para>
  48
  49   <para>
  50    Normally, the creator becomes the owner of the new database.
  51    Superusers can create databases owned by other users, by using the
  52    <literal>OWNER</> clause. They can even create databases owned by
  53    users with no special privileges. Non-superusers with <literal>CREATEDB</>
  54    privilege can only create databases owned by themselves.
  55   </para>
  56
  57   <para>
  58    By default, the new database will be created by cloning the standard
  59    system database <literal>template1</>.  A different template can be
  60    specified by writing <literal>TEMPLATE
  61    <replaceable class="parameter">name</replaceable></literal>.  In particular,
  62    by writing <literal>TEMPLATE template0</>, you can create a virgin
  63    database containing only the standard objects predefined by your
  64    version of <productname>PostgreSQL</productname>.  This is useful
  65    if you wish to avoid copying
  66    any installation-local objects that might have been added to
  67    <literal>template1</>.
  68   </para>
  69  </refsect1>
  70
  71  <refsect1>
  72   <title>Parameters</title>
  73
  74     <variablelist>
  75      <varlistentry>
  76       <term><replaceable class="parameter">name</replaceable></term>
  77       <listitem>
  78        <para>
  79         The name of a database to create.
  80        </para>
  81       </listitem>
  82      </varlistentry>
  83      <varlistentry>
  84       <term><replaceable class="parameter">dbowner</replaceable></term>
  85       <listitem>
  86        <para>
  87         The name of the database user who will own the new database,
  88         or <literal>DEFAULT</literal> to use the default (namely, the
  89         user executing the command).
  90        </para>
  91       </listitem>
  92      </varlistentry>
  93      <varlistentry>
  94       <term><replaceable class="parameter">template</replaceable></term>
  95       <listitem>
  96        <para>
  97         The name of the template from which to create the new database,
  98         or <literal>DEFAULT</literal> to use the default template
  99         (<literal>template1</literal>).
 100        </para>
 101       </listitem>
 102      </varlistentry>
 103      <varlistentry>
 104       <term><replaceable class="parameter">encoding</replaceable></term>
 105       <listitem>
 106        <para>
 107         Character set encoding to use in the new database.  Specify
 108         a string constant (e.g., <literal>'SQL_ASCII'</literal>),
 109         or an integer encoding number, or <literal>DEFAULT</literal>
 110         to use the default encoding (namely, the encoding of the
 111         template database). The character sets supported by the
 112         <productname>PostgreSQL</productname> server are described in
 113         <xref linkend="multibyte-charset-supported">. See below for
 114         additional restrictions.
 115        </para>
 116       </listitem>
 117      </varlistentry>
 118      <varlistentry>
 119       <term><replaceable class="parameter">lc_collate</replaceable></term>
 120       <listitem>
 121        <para>
 122         Collation order (<literal>LC_COLLATE</>) to use in the new database.
 123         This affects the sort order applied to strings, e.g. in queries with
 124         ORDER BY, as well as the order used in indexes on text columns.
 125         The default is to use the collation order of the template database.
 126         See below for additional restrictions.
 127        </para>
 128       </listitem>
 129      </varlistentry>
 130      <varlistentry>
 131       <term><replaceable class="parameter">lc_ctype</replaceable></term>
 132       <listitem>
 133        <para>
 134         Character classification (<literal>LC_CTYPE</>) to use in the new
 135         database. This affects the categorization of characters, e.g. lower,
 136         upper and digit. The default is to use the character classification of
 137         the template database. See below for additional restrictions.
 138        </para>
 139       </listitem>
 140      </varlistentry>
 141      <varlistentry>
 142       <term><replaceable class="parameter">tablespace</replaceable></term>
 143       <listitem>
 144        <para>
 145         The name of the tablespace that will be associated with the
 146         new database, or <literal>DEFAULT</literal> to use the
 147         template database's tablespace. This
 148         tablespace will be the default tablespace used for objects
 149         created in this database. See
 150         <xref linkend="sql-createtablespace" endterm="sql-createtablespace-title">
 151         for more information.
 152        </para>
 153       </listitem>
 154      </varlistentry>
 155
 156      <varlistentry>
 157       <term><replaceable class="parameter">connlimit</replaceable></term>
 158       <listitem>
 159        <para>
 160         How many concurrent connections can be made
 161         to this database.  -1 (the default) means no limit.
 162        </para>
 163       </listitem>
 164      </varlistentry>
 165     </variablelist>
 166
 167   <para>
 168    Optional parameters can be written in any order, not only the order
 169    illustrated above.
 170   </para>
 171  </refsect1>
 172
 173  <refsect1>
 174   <title>Notes</title>
 175
 176    <para>
 177     <command>CREATE DATABASE</> cannot be executed inside a transaction
 178     block.
 179    </para>
 180
 181    <para>
 182     Errors along the line of <quote>could not initialize database directory</>
 183     are most likely related to insufficient permissions on the data
 184     directory, a full disk, or other file system problems.
 185    </para>
 186
 187    <para>
 188     Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
 189    </para>
 190
 191    <para>
 192     The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
 193     wrapper program around this command, provided for convenience.
 194    </para>
 195
 196   <para>
 197    Although it is possible to copy a database other than <literal>template1</>
 198    by specifying its name as the template, this is not (yet) intended as
 199    a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
 200    The principal limitation is that no other sessions can be connected to
 201    the template database while it is being copied.  <command>CREATE
 202    DATABASE</> will fail if any other connection exists when it starts;
 203    otherwise, new connections to the template database are locked out
 204    until <command>CREATE DATABASE</> completes.
 205    See <xref linkend="manage-ag-templatedbs"> for more information.
 206   </para>
 207
 208   <para>
 209    The character set encoding specified for the new database must be
 210    compatible with the chosen locale settings (<literal>LC_COLLATE</> and
 211    <literal>LC_CTYPE</>).  If the locale is <literal>C</> (or equivalently
 212    <literal>POSIX</>), then all encodings are allowed, but for other
 213    locale settings there is only one encoding that will work properly.
 214    (On Windows, however, UTF-8 encoding can be used with any locale.)
 215    <command>CREATE DATABASE</> will allow superusers to specify
 216    <literal>SQL_ASCII</> encoding regardless of the locale settings,
 217    but this choice is deprecated and may result in misbehavior of
 218    character-string functions if data that is not encoding-compatible
 219    with the locale is stored in the database.
 220   </para>
 221
 222   <para>
 223    The encoding and locale settings must match those of the template database,
 224    except when <literal>template0</> is used as template.  This is because
 225    other databases might contain data that does not match the specified
 226    encoding, or might contain indexes whose sort ordering is affected by
 227    <literal>LC_COLLATE</> and <literal>LC_CTYPE</>.  Copying such data would
 228    result in a database that is corrupt according to the new settings.
 229    <literal>template0</literal>, however, is known to not contain any data or
 230    indexes that would be affected.
 231   </para>
 232
 233   <para>
 234    The <literal>CONNECTION LIMIT</> option is only enforced approximately;
 235    if two new sessions start at about the same time when just one
 236    connection <quote>slot</> remains for the database, it is possible that
 237    both will fail.  Also, the limit is not enforced against superusers.
 238   </para>
 239  </refsect1>
 240
 241  <refsect1>
 242   <title>Examples</title>
 243
 244   <para>
 245    To create a new database:
 246
 247 <programlisting>
 248 CREATE DATABASE lusiadas;
 249 </programlisting>
 250   </para>
 251
 252   <para>
 253    To create a database <literal>sales</> owned by user <literal>salesapp</>
 254    with a default tablespace of <literal>salesspace</>:
 255
 256 <programlisting>
 257 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
 258 </programlisting>
 259   </para>
 260
 261   <para>
 262    To create a database <literal>music</> which supports the ISO-8859-1
 263    character set:
 264
 265 <programlisting>
 266 CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;
 267 </programlisting>
 268
 269    In this example, the <literal>TEMPLATE template0</> clause would only
 270    be required if <literal>template1</>'s encoding is not ISO-8859-1.
 271    Note that changing encoding might require selecting new
 272    <literal>LC_COLLATE</> and <literal>LC_CTYPE</> settings as well.
 273   </para>
 274  </refsect1>
 275
 276  <refsect1>
 277   <title>Compatibility</title>
 278
 279   <para>
 280    There is no <command>CREATE DATABASE</command> statement in the SQL
 281    standard.  Databases are equivalent to catalogs, whose creation is
 282    implementation-defined.
 283   </para>
 284  </refsect1>
 285
 286  <refsect1>
 287   <title>See Also</title>
 288
 289   <simplelist type="inline">
 290    <member><xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title"></member>
 291    <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>
 292   </simplelist>
 293  </refsect1>
 294
 295 </refentry>