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