doc/src/sgml/ref/create_database.sgml

<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.46 2006/09/16 00:30:17 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-CREATEDATABASE">
 <refmeta>
  <refentrytitle id="sql-createdatabase-title">CREATE DATABASE</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE DATABASE</refname>
  <refpurpose>create a new database</refpurpose>
 </refnamediv>

 <indexterm zone="sql-createdatabase">
  <primary>CREATE DATABASE</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
    [ [ WITH ] [ OWNER [=] <replaceable class="parameter">dbowner</replaceable> ]
           [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
           [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
           [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ]
           [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE DATABASE</command> creates a new
   <productname>PostgreSQL</productname> database.
  </para>

  <para>
   To create a database, you must be a superuser or have the special
   <literal>CREATEDB</> privilege.
   See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
  </para>

  <para>
   Normally, the creator becomes the owner of the new database.
   Superusers can create databases owned by other users, by using the
   <literal>OWNER</> clause. They can even create databases owned by
   users with no special privileges. Non-superusers with <literal>CREATEDB</>
   privilege can only create databases owned by themselves.
  </para>

  <para>
   By default, the new database will be created by cloning the standard
   system database <literal>template1</>.  A different template can be
   specified by writing <literal>TEMPLATE
   <replaceable class="parameter">name</replaceable></literal>.  In particular,
   by writing <literal>TEMPLATE template0</>, you can create a virgin
   database containing only the standard objects predefined by your
   version of <productname>PostgreSQL</productname>.  This is useful
   if you wish to avoid copying
   any installation-local objects that may have been added to
   <literal>template1</>.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
        The name of a database to create.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">dbowner</replaceable></term>
      <listitem>
       <para>
        The name of the database user who will own the new database,
        or <literal>DEFAULT</literal> to use the default (namely, the
        user executing the command).
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">template</replaceable></term>
      <listitem>
       <para>
        The name of the template from which to create the new database,
        or <literal>DEFAULT</literal> to use the default template
        (<literal>template1</literal>).
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">encoding</replaceable></term>
      <listitem>
       <para>
        Character set encoding to use in the new database.  Specify
        a string constant (e.g., <literal>'SQL_ASCII'</literal>),
        or an integer encoding number, or <literal>DEFAULT</literal>
        to use the default encoding (namely, the encoding of the
        template database). The character sets supported by the
        <productname>PostgreSQL</productname> server are described in
        <xref linkend="multibyte-charset-supported">.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">tablespace</replaceable></term>
      <listitem>
       <para>
        The name of the tablespace that will be associated with the
        new database, or <literal>DEFAULT</literal> to use the
        template database's tablespace. This
        tablespace will be the default tablespace used for objects
        created in this database. See
        <xref linkend="sql-createtablespace" endterm="sql-createtablespace-title">
        for more information.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">connlimit</replaceable></term>
      <listitem>
       <para>
        How many concurrent connections can be made
        to this database.  -1 (the default) means no limit.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

  <para>
   Optional parameters can be written in any order, not only the order
   illustrated above.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

   <para>
    <command>CREATE DATABASE</> cannot be executed inside a transaction
    block.
   </para>

   <para>
    Errors along the line of <quote>could not initialize database directory</>
    are most likely related to insufficient permissions on the data
    directory, a full disk, or other file system problems.
   </para>

   <para>
    Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
   </para>

   <para>
    The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
    wrapper program around this command, provided for convenience.
   </para>

  <para>
   Although it is possible to copy a database other than <literal>template1</>
   by specifying its name as the template, this is not (yet) intended as
   a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
   The principal limitation is that no other sessions can be connected to
   the template database while it is being copied.  <command>CREATE
   DATABASE</> will fail if any other connection exists when it starts;
   otherwise, new connections to the template database are locked out
   until <command>CREATE DATABASE</> completes.
   See <xref linkend="manage-ag-templatedbs"> for more information.
  </para>

  <para>
   The <literal>CONNECTION LIMIT</> option is only enforced approximately;
   if two new sessions start at about the same time when just one
   connection <quote>slot</> remains for the database, it is possible that
   both will fail.  Also, the limit is not enforced against superusers.
  </para>

  <para>
   User can not create database named "hcatalog", because it's reserved for HCatalog integration feature.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To create a new database:

<programlisting>
CREATE DATABASE lusiadas;
</programlisting>
  </para>

  <para>
   To create a database <literal>sales</> owned by user <literal>salesapp</>
   with a default tablespace of <literal>salesspace</>:

<programlisting>
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
</programlisting>
  </para>

  <para>
   To create a database <literal>music</> which supports the ISO-8859-1 
   character set:

<programlisting>
CREATE DATABASE music ENCODING 'LATIN1';
</programlisting>
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   There is no <command>CREATE DATABASE</command> statement in the SQL
   standard.  Databases are equivalent to catalogs, whose creation is
   implementation-defined.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title"></member>
   <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>
  </simplelist>
 </refsect1>

</refentry>