<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.47 2007/01/31 23:26:03 momjian Exp $
+doc/src/sgml/ref/create_database.sgml
PostgreSQL documentation
-->
<refentry id="SQL-CREATEDATABASE">
+ <indexterm zone="sql-createdatabase">
+ <primary>CREATE DATABASE</primary>
+ </indexterm>
+
<refmeta>
- <refentrytitle id="sql-createdatabase-title">CREATE DATABASE</refentrytitle>
+ <refentrytitle>CREATE DATABASE</refentrytitle>
+ <manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<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> ]
+ [ [ WITH ] [ OWNER [=] <replaceable class="parameter">user_name</replaceable> ]
[ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
[ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
- [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ]
+ [ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
+ [ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
+ [ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
+ [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
+ [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
[ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
</synopsis>
</refsynopsisdiv>
<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.
+ See <xref linkend="SQL-CREATEUSER">.
</para>
<para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">dbowner</replaceable></term>
+ <term><replaceable class="parameter">user_name</replaceable></term>
<listitem>
<para>
- The name of the database user who will own the new database,
+ The role name of the user who will own the new database,
or <literal>DEFAULT</literal> to use the default (namely, the
- user executing the command).
+ user executing the command). To create a database owned by another
+ role, you must be a direct or indirect member of that role,
+ or be a superuser.
</para>
</listitem>
</varlistentry>
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">.
+ <xref linkend="multibyte-charset-supported">. See below for
+ additional restrictions.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">lc_collate</replaceable></term>
+ <listitem>
+ <para>
+ Collation order (<literal>LC_COLLATE</>) to use in the new database.
+ This affects the sort order applied to strings, e.g. in queries with
+ ORDER BY, as well as the order used in indexes on text columns.
+ The default is to use the collation order of the template database.
+ See below for additional restrictions.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">tablespace</replaceable></term>
+ <term><replaceable class="parameter">lc_ctype</replaceable></term>
+ <listitem>
+ <para>
+ Character classification (<literal>LC_CTYPE</>) to use in the new
+ database. This affects the categorization of characters, e.g. lower,
+ upper and digit. The default is to use the character classification of
+ the template database. See below for additional restrictions.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">tablespace_name</replaceable></term>
<listitem>
<para>
The name of the tablespace that will be associated with 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">
+ <xref linkend="sql-createtablespace">
for more information.
</para>
</listitem>
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">istemplate</replaceable></term>
+ <listitem>
+ <para>
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false (the default), then only superusers or the owner
+ of the database can clone it.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">allowconn</replaceable></term>
+ <listitem>
+ <para>
+ If false then no one can connect to this database. The default is
+ true, allowing connections (except as restricted by other mechanisms,
+ such as <literal>GRANT</>/<literal>REVOKE CONNECT</>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">connlimit</replaceable></term>
<listitem>
<para>
</para>
<para>
- Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
+ Use <xref linkend="SQL-DROPDATABASE"> to remove a database.
</para>
<para>
- The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
+ The program <xref linkend="APP-CREATEDB"> is a
wrapper program around this command, provided for convenience.
</para>
+ <para>
+ Database-level configuration parameters (set via <xref
+ linkend="sql-alterdatabase">) are not copied from the template
+ database.
+ </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
See <xref linkend="manage-ag-templatedbs"> for more information.
</para>
+ <para>
+ The character set encoding specified for the new database must be
+ compatible with the chosen locale settings (<literal>LC_COLLATE</> and
+ <literal>LC_CTYPE</>). If the locale is <literal>C</> (or equivalently
+ <literal>POSIX</>), then all encodings are allowed, but for other
+ locale settings there is only one encoding that will work properly.
+ (On Windows, however, UTF-8 encoding can be used with any locale.)
+ <command>CREATE DATABASE</> will allow superusers to specify
+ <literal>SQL_ASCII</> encoding regardless of the locale settings,
+ but this choice is deprecated and may result in misbehavior of
+ character-string functions if data that is not encoding-compatible
+ with the locale is stored in the database.
+ </para>
+
+ <para>
+ The encoding and locale settings must match those of the template database,
+ except when <literal>template0</> is used as template. This is because
+ other databases might contain data that does not match the specified
+ encoding, or might contain indexes whose sort ordering is affected by
+ <literal>LC_COLLATE</> and <literal>LC_CTYPE</>. Copying such data would
+ result in a database that is corrupt according to the new settings.
+ <literal>template0</literal>, however, is known to not contain any data or
+ indexes that would be affected.
+ </para>
+
<para>
The <literal>CONNECTION LIMIT</> option is only enforced approximately;
if two new sessions start at about the same time when just one
</para>
<para>
- To create a database <literal>music</> which supports the ISO-8859-1
+ To create a database <literal>music</> which supports the ISO-8859-1
character set:
<programlisting>
-CREATE DATABASE music ENCODING 'LATIN1';
+CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;
</programlisting>
+
+ In this example, the <literal>TEMPLATE template0</> clause would only
+ be required if <literal>template1</>'s encoding is not ISO-8859-1.
+ Note that changing encoding might require selecting new
+ <literal>LC_COLLATE</> and <literal>LC_CTYPE</> settings as well.
</para>
</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>
+ <member><xref linkend="sql-alterdatabase"></member>
+ <member><xref linkend="sql-dropdatabase"></member>
</simplelist>
</refsect1>
projects / postgresql / blobdiff