2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.50 2008/11/14 10:22:46 petere Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEDATABASE">
8 <refentrytitle id="sql-createdatabase-title">CREATE DATABASE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE DATABASE</refname>
15 <refpurpose>create a new database</refpurpose>
18 <indexterm zone="sql-createdatabase">
19 <primary>CREATE DATABASE</primary>
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 [ COLLATE [=] <replaceable class="parameter">collate</replaceable> ]
29 [ CTYPE [=] <replaceable class="parameter">ctype</replaceable> ]
30 [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ]
31 [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
36 <title>Description</title>
39 <command>CREATE DATABASE</command> creates a new
40 <productname>PostgreSQL</productname> database.
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">.
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.
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</>.
72 <title>Parameters</title>
76 <term><replaceable class="parameter">name</replaceable></term>
79 The name of a database to create.
84 <term><replaceable class="parameter">dbowner</replaceable></term>
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).
94 <term><replaceable class="parameter">template</replaceable></term>
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>).
104 <term><replaceable class="parameter">encoding</replaceable></term>
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.
119 <term><replaceable class="parameter">collate</replaceable></term>
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.
131 <term><replaceable class="parameter">ctype</replaceable></term>
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.
142 <term><replaceable class="parameter">tablespace</replaceable></term>
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.
157 <term><replaceable class="parameter">connlimit</replaceable></term>
160 How many concurrent connections can be made
161 to this database. -1 (the default) means no limit.
168 Optional parameters can be written in any order, not only the order
177 <command>CREATE DATABASE</> cannot be executed inside a transaction
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.
188 Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
192 The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
193 wrapper program around this command, provided for convenience.
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.
209 The character set encoding specified for the new database must be
210 compatible with the chosen COLLATE and CTYPE settings.
211 If <envar>LC_CTYPE</> 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 <command>CREATE DATABASE</> will allow superusers to specify
215 <literal>SQL_ASCII</> encoding regardless of the locale setting,
216 but this choice is deprecated and may result in misbehavior of
217 character-string functions if data that is not encoding-compatible
218 with the locale is stored in the database.
222 The <literal>COLLATE</> and <literal>CTYPE</> settings must match
223 those of the template database, except when template0 is used as
224 template. This is because <literal>COLLATE</> and <literal>CTYPE</>
225 affects the ordering in indexes, so that any indexes copied from the
226 template database would be invalid in the new database with different
227 settings. <literal>template0</literal>, however, is known to not
228 contain any indexes that would be affected.
232 The <literal>CONNECTION LIMIT</> option is only enforced approximately;
233 if two new sessions start at about the same time when just one
234 connection <quote>slot</> remains for the database, it is possible that
235 both will fail. Also, the limit is not enforced against superusers.
240 <title>Examples</title>
243 To create a new database:
246 CREATE DATABASE lusiadas;
251 To create a database <literal>sales</> owned by user <literal>salesapp</>
252 with a default tablespace of <literal>salesspace</>:
255 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
260 To create a database <literal>music</> which supports the ISO-8859-1
264 CREATE DATABASE music ENCODING 'LATIN1';
270 <title>Compatibility</title>
273 There is no <command>CREATE DATABASE</command> statement in the SQL
274 standard. Databases are equivalent to catalogs, whose creation is
275 implementation-defined.
280 <title>See Also</title>
282 <simplelist type="inline">
283 <member><xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title"></member>
284 <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>