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 [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
32 [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
33 [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</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">allowconn</replaceable></term>
156 If false then no one can connect to this database. The default is
157 true, allowing connections (except as restricted by other mechanisms,
158 such as <literal>GRANT</>/<literal>REVOKE CONNECT</>).
164 <term><replaceable class="parameter">connlimit</replaceable></term>
167 How many concurrent connections can be made
168 to this database. -1 (the default) means no limit.
174 <term><replaceable class="parameter">istemplate</replaceable></term>
177 If true, then this database can be cloned by any user with <literal>CREATEDB</literal>
178 privileges; if false (the default), then only superusers or the owner
179 of the database can clone it.
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 or
262 background worker processes.
267 <title>Examples</title>
270 To create a new database:
273 CREATE DATABASE lusiadas;
278 To create a database <literal>sales</> owned by user <literal>salesapp</>
279 with a default tablespace of <literal>salesspace</>:
282 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
287 To create a database <literal>music</> with a different locale:
289 CREATE DATABASE music
290 LC_COLLATE 'sv_SE.utf8' LC_CTYPE 'sv_SE.utf8'
293 In this example, the <literal>TEMPLATE template0</> clause is required if
294 the specified locale is different from the one in <literal>template1</>.
295 (If it is not, then specifying the locale explicitly is redundant.)
299 To create a database <literal>music2</> with a different locale and a
300 different character set encoding:
302 CREATE DATABASE music2
303 LC_COLLATE 'sv_SE.iso885915' LC_CTYPE 'sv_SE.iso885915'
307 The specified locale and encoding settings must match, or an error will be
312 Note that locale names are specific to the operating system, so that the
313 above commands might not work in the same way everywhere.
318 <title>Compatibility</title>
321 There is no <command>CREATE DATABASE</command> statement in the SQL
322 standard. Databases are equivalent to catalogs, whose creation is
323 implementation-defined.
328 <title>See Also</title>
330 <simplelist type="inline">
331 <member><xref linkend="sql-alterdatabase"></member>
332 <member><xref linkend="sql-dropdatabase"></member>