2 doc/src/sgml/ref/create_database.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEDATABASE">
7 <indexterm zone="sql-createdatabase">
8 <primary>CREATE DATABASE</primary>
12 <refentrytitle>CREATE DATABASE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>CREATE DATABASE</refname>
19 <refpurpose>create a new database</refpurpose>
24 CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
25 [ [ WITH ] [ OWNER [=] <replaceable class="parameter">user_name</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_name</replaceable> ]
31 [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
32 [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
33 [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
38 <title>Description</title>
41 <command>CREATE DATABASE</command> creates a new
42 <productname>PostgreSQL</productname> database.
46 To create a database, you must be a superuser or have the special
47 <literal>CREATEDB</> privilege.
48 See <xref linkend="SQL-CREATEUSER">.
52 By default, the new database will be created by cloning the standard
53 system database <literal>template1</>. A different template can be
54 specified by writing <literal>TEMPLATE
55 <replaceable class="parameter">name</replaceable></literal>. In particular,
56 by writing <literal>TEMPLATE template0</>, you can create a virgin
57 database containing only the standard objects predefined by your
58 version of <productname>PostgreSQL</productname>. This is useful
59 if you wish to avoid copying
60 any installation-local objects that might have been added to
61 <literal>template1</>.
66 <title>Parameters</title>
70 <term><replaceable class="parameter">name</replaceable></term>
73 The name of a database to create.
78 <term><replaceable class="parameter">user_name</replaceable></term>
81 The role name of the user who will own the new database,
82 or <literal>DEFAULT</literal> to use the default (namely, the
83 user executing the command). To create a database owned by another
84 role, you must be a direct or indirect member of that role,
90 <term><replaceable class="parameter">template</replaceable></term>
93 The name of the template from which to create the new database,
94 or <literal>DEFAULT</literal> to use the default template
95 (<literal>template1</literal>).
100 <term><replaceable class="parameter">encoding</replaceable></term>
103 Character set encoding to use in the new database. Specify
104 a string constant (e.g., <literal>'SQL_ASCII'</literal>),
105 or an integer encoding number, or <literal>DEFAULT</literal>
106 to use the default encoding (namely, the encoding of the
107 template database). The character sets supported by the
108 <productname>PostgreSQL</productname> server are described in
109 <xref linkend="multibyte-charset-supported">. See below for
110 additional restrictions.
115 <term><replaceable class="parameter">lc_collate</replaceable></term>
118 Collation order (<literal>LC_COLLATE</>) to use in the new database.
119 This affects the sort order applied to strings, e.g. in queries with
120 ORDER BY, as well as the order used in indexes on text columns.
121 The default is to use the collation order of the template database.
122 See below for additional restrictions.
127 <term><replaceable class="parameter">lc_ctype</replaceable></term>
130 Character classification (<literal>LC_CTYPE</>) to use in the new
131 database. This affects the categorization of characters, e.g. lower,
132 upper and digit. The default is to use the character classification of
133 the template database. See below for additional restrictions.
138 <term><replaceable class="parameter">tablespace_name</replaceable></term>
141 The name of the tablespace that will be associated with the
142 new database, or <literal>DEFAULT</literal> to use the
143 template database's tablespace. This
144 tablespace will be the default tablespace used for objects
145 created in this database. See
146 <xref linkend="sql-createtablespace">
147 for more information.
153 <term><replaceable class="parameter">istemplate</replaceable></term>
156 If true, then this database can be cloned by any user with CREATEDB
157 privileges; if false (the default), then only superusers or the owner
158 of the database can clone it.
164 <term><replaceable class="parameter">allowconn</replaceable></term>
167 If false then no one can connect to this database. The default is
168 true, allowing connections (except as restricted by other mechanisms,
169 such as <literal>GRANT</>/<literal>REVOKE CONNECT</>).
175 <term><replaceable class="parameter">connlimit</replaceable></term>
178 How many concurrent connections can be made
179 to this database. -1 (the default) means no limit.
186 Optional parameters can be written in any order, not only the order
195 <command>CREATE DATABASE</> cannot be executed inside a transaction
200 Errors along the line of <quote>could not initialize database directory</>
201 are most likely related to insufficient permissions on the data
202 directory, a full disk, or other file system problems.
206 Use <xref linkend="SQL-DROPDATABASE"> to remove a database.
210 The program <xref linkend="APP-CREATEDB"> is a
211 wrapper program around this command, provided for convenience.
215 Database-level configuration parameters (set via <xref
216 linkend="sql-alterdatabase">) are not copied from the template
221 Although it is possible to copy a database other than <literal>template1</>
222 by specifying its name as the template, this is not (yet) intended as
223 a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
224 The principal limitation is that no other sessions can be connected to
225 the template database while it is being copied. <command>CREATE
226 DATABASE</> will fail if any other connection exists when it starts;
227 otherwise, new connections to the template database are locked out
228 until <command>CREATE DATABASE</> completes.
229 See <xref linkend="manage-ag-templatedbs"> for more information.
233 The character set encoding specified for the new database must be
234 compatible with the chosen locale settings (<literal>LC_COLLATE</> and
235 <literal>LC_CTYPE</>). If the locale is <literal>C</> (or equivalently
236 <literal>POSIX</>), then all encodings are allowed, but for other
237 locale settings there is only one encoding that will work properly.
238 (On Windows, however, UTF-8 encoding can be used with any locale.)
239 <command>CREATE DATABASE</> will allow superusers to specify
240 <literal>SQL_ASCII</> encoding regardless of the locale settings,
241 but this choice is deprecated and may result in misbehavior of
242 character-string functions if data that is not encoding-compatible
243 with the locale is stored in the database.
247 The encoding and locale settings must match those of the template database,
248 except when <literal>template0</> is used as template. This is because
249 other databases might contain data that does not match the specified
250 encoding, or might contain indexes whose sort ordering is affected by
251 <literal>LC_COLLATE</> and <literal>LC_CTYPE</>. Copying such data would
252 result in a database that is corrupt according to the new settings.
253 <literal>template0</literal>, however, is known to not contain any data or
254 indexes that would be affected.
258 The <literal>CONNECTION LIMIT</> option is only enforced approximately;
259 if two new sessions start at about the same time when just one
260 connection <quote>slot</> remains for the database, it is possible that
261 both will fail. Also, the limit is not enforced against superusers.
266 <title>Examples</title>
269 To create a new database:
272 CREATE DATABASE lusiadas;
277 To create a database <literal>sales</> owned by user <literal>salesapp</>
278 with a default tablespace of <literal>salesspace</>:
281 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
286 To create a database <literal>music</> which supports the ISO-8859-1
290 CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;
293 In this example, the <literal>TEMPLATE template0</> clause would only
294 be required if <literal>template1</>'s encoding is not ISO-8859-1.
295 Note that changing encoding might require selecting new
296 <literal>LC_COLLATE</> and <literal>LC_CTYPE</> settings as well.
301 <title>Compatibility</title>
304 There is no <command>CREATE DATABASE</command> statement in the SQL
305 standard. Databases are equivalent to catalogs, whose creation is
306 implementation-defined.
311 <title>See Also</title>
313 <simplelist type="inline">
314 <member><xref linkend="sql-alterdatabase"></member>
315 <member><xref linkend="sql-dropdatabase"></member>