1CREATE DATABASE(7) PostgreSQL 16.1 Documentation CREATE DATABASE(7)
2
6 CREATE_DATABASE - create a new database
7
9 CREATE DATABASE name
10 [ WITH ] [ OWNER [=] user_name ]
11 [ TEMPLATE [=] template ]
12 [ ENCODING [=] encoding ]
13 [ STRATEGY [=] strategy ] ]
14 [ LOCALE [=] locale ]
15 [ LC_COLLATE [=] lc_collate ]
16 [ LC_CTYPE [=] lc_ctype ]
17 [ ICU_LOCALE [=] icu_locale ]
18 [ ICU_RULES [=] icu_rules ]
19 [ LOCALE_PROVIDER [=] locale_provider ]
20 [ COLLATION_VERSION = collation_version ]
21 [ TABLESPACE [=] tablespace_name ]
22 [ ALLOW_CONNECTIONS [=] allowconn ]
23 [ CONNECTION LIMIT [=] connlimit ]
24 [ IS_TEMPLATE [=] istemplate ]
25 [ OID [=] oid ]
26
28 CREATE DATABASE creates a new PostgreSQL database.
29 To create a database, you must be a superuser or have the special
31 CREATEDB privilege. See CREATE ROLE (CREATE_ROLE(7)).
32 By default, the new database will be created by cloning the standard
34 system database template1. A different template can be specified by
35 writing TEMPLATE name. In particular, by writing TEMPLATE template0,
36 you can create a pristine database (one where no user-defined objects
37 exist and where the system objects have not been altered) containing
38 only the standard objects predefined by your version of PostgreSQL.
39 This is useful if you wish to avoid copying any installation-local
40 objects that might have been added to template1.
41
43 name
44 The name of a database to create.
45 user_name
47 The role name of the user who will own the new database, or DEFAULT
48 to use the default (namely, the user executing the command). To
49 create a database owned by another role, you must be able to SET
50 ROLE to that role.
51 template
53 The name of the template from which to create the new database, or
54 DEFAULT to use the default template (template1).
55 encoding
57 Character set encoding to use in the new database. Specify a string
58 constant (e.g., 'SQL_ASCII'), or an integer encoding number, or
59 DEFAULT to use the default encoding (namely, the encoding of the
60 template database). The character sets supported by the PostgreSQL
61 server are described in Section 24.3.1. See below for additional
62 restrictions.
63 strategy
65 Strategy to be used in creating the new database. If the WAL_LOG
66 strategy is used, the database will be copied block by block and
67 each block will be separately written to the write-ahead log. This
68 is the most efficient strategy in cases where the template database
69 is small, and therefore it is the default. The older FILE_COPY
70 strategy is also available. This strategy writes a small record to
71 the write-ahead log for each tablespace used by the target
72 database. Each such record represents copying an entire directory
73 to a new location at the filesystem level. While this does reduce
74 the write-ahead log volume substantially, especially if the
75 template database is large, it also forces the system to perform a
76 checkpoint both before and after the creation of the new database.
77 In some situations, this may have a noticeable negative impact on
78 overall system performance.
79 locale
81 Sets the default collation order and character classification in
82 the new database. Collation affects the sort order applied to
83 strings, e.g., in queries with ORDER BY, as well as the order used
84 in indexes on text columns. Character classification affects the
85 categorization of characters, e.g., lower, upper, and digit. Also
86 sets the associated aspects of the operating system environment,
87 LC_COLLATE and LC_CTYPE. The default is the same setting as the
88 template database. See Section 24.2.2.3.1 and Section 24.2.2.3.2
89 for details.
90 Can be overridden by setting lc_collate, lc_ctype, or icu_locale
92 individually.
93 Tip
95 The other locale settings lc_messages, lc_monetary, lc_numeric,
96 and lc_time are not fixed per database and are not set by this
97 command. If you want to make them the default for a specific
98 database, you can use ALTER DATABASE ... SET.
99 lc_collate
101 Sets LC_COLLATE in the database server's operating system
102 environment. The default is the setting of locale if specified,
103 otherwise the same setting as the template database. See below for
104 additional restrictions.
105 If locale_provider is libc, also sets the default collation order
107 to use in the new database, overriding the setting locale.
108 lc_ctype
110 Sets LC_CTYPE in the database server's operating system
111 environment. The default is the setting of locale if specified,
112 otherwise the same setting as the template database. See below for
113 additional restrictions.
114 If locale_provider is libc, also sets the default character
116 classification to use in the new database, overriding the setting
117 locale.
118 icu_locale
120 Specifies the ICU locale (see Section 24.2.2.3.2) for the database
121 default collation order and character classification, overriding
122 the setting locale. The locale provider must be ICU. The default is
123 the setting of locale if specified; otherwise the same setting as
124 the template database.
125 icu_rules
127 Specifies additional collation rules to customize the behavior of
128 the default collation of this database. This is supported for ICU
129 only. See Section 24.2.3.4 for details.
130 locale_provider
132 Specifies the provider to use for the default collation in this
133 database. Possible values are icu (if the server was built with ICU
134 support) or libc. By default, the provider is the same as that of
135 the template. See Section 24.1.4 for details.
136 collation_version
138 Specifies the collation version string to store with the database.
139 Normally, this should be omitted, which will cause the version to
140 be computed from the actual version of the database collation as
141 provided by the operating system. This option is intended to be
142 used by pg_upgrade for copying the version from an existing
143 installation.
144 See also ALTER DATABASE (ALTER_DATABASE(7)) for how to handle
146 database collation version mismatches.
147 tablespace_name
149 The name of the tablespace that will be associated with the new
150 database, or DEFAULT to use the template database's tablespace.
151 This tablespace will be the default tablespace used for objects
152 created in this database. See CREATE TABLESPACE
153 (CREATE_TABLESPACE(7)) for more information.
154 allowconn
156 If false then no one can connect to this database. The default is
157 true, allowing connections (except as restricted by other
158 mechanisms, such as GRANT/REVOKE CONNECT).
159 connlimit
161 How many concurrent connections can be made to this database. -1
162 (the default) means no limit.
163 istemplate
165 If true, then this database can be cloned by any user with CREATEDB
166 privileges; if false (the default), then only superusers or the
167 owner of the database can clone it.
168 oid
170 The object identifier to be used for the new database. If this
171 parameter is not specified, PostgreSQL will choose a suitable OID
172 automatically. This parameter is primarily intended for internal
173 use by pg_upgrade, and only pg_upgrade can specify a value less
174 than 16384.
175 Optional parameters can be written in any order, not only the order
177 illustrated above.
178
180 CREATE DATABASE cannot be executed inside a transaction block.
181 Errors along the line of “could not initialize database directory” are
183 most likely related to insufficient permissions on the data directory,
184 a full disk, or other file system problems.
185 Use DROP DATABASE to remove a database.
187 The program createdb(1) is a wrapper program around this command,
189 provided for convenience.
190 Database-level configuration parameters (set via ALTER DATABASE) and
192 database-level permissions (set via GRANT) are not copied from the
193 template database.
194 Although it is possible to copy a database other than template1 by
196 specifying its name as the template, this is not (yet) intended as a
197 general-purpose “COPY DATABASE” facility. The principal limitation is
198 that no other sessions can be connected to the template database while
199 it is being copied. CREATE DATABASE will fail if any other connection
200 exists when it starts; otherwise, new connections to the template
201 database are locked out until CREATE DATABASE completes. See
202 Section 23.3 for more information.
203 The character set encoding specified for the new database must be
205 compatible with the chosen locale settings (LC_COLLATE and LC_CTYPE).
206 If the locale is C (or equivalently POSIX), then all encodings are
207 allowed, but for other locale settings there is only one encoding that
208 will work properly. (On Windows, however, UTF-8 encoding can be used
209 with any locale.) CREATE DATABASE will allow superusers to specify
210 SQL_ASCII encoding regardless of the locale settings, but this choice
211 is deprecated and may result in misbehavior of character-string
212 functions if data that is not encoding-compatible with the locale is
213 stored in the database.
214 The encoding and locale settings must match those of the template
216 database, except when template0 is used as template. This is because
217 other databases might contain data that does not match the specified
218 encoding, or might contain indexes whose sort ordering is affected by
219 LC_COLLATE and LC_CTYPE. Copying such data would result in a database
220 that is corrupt according to the new settings. template0, however, is
221 known to not contain any data or indexes that would be affected.
222 There is currently no option to use a database locale with
224 nondeterministic comparisons (see CREATE COLLATION for an explanation).
225 If this is needed, then per-column collations would need to be used.
226 The CONNECTION LIMIT option is only enforced approximately; if two new
228 sessions start at about the same time when just one connection “slot”
229 remains for the database, it is possible that both will fail. Also, the
230 limit is not enforced against superusers or background worker
231 processes.
232
234 To create a new database:
235 CREATE DATABASE lusiadas;
237 To create a database sales owned by user salesapp with a default
239 tablespace of salesspace:
240 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
242 To create a database music with a different locale:
244 CREATE DATABASE music
246 LOCALE 'sv_SE.utf8'
247 TEMPLATE template0;
248 In this example, the TEMPLATE template0 clause is required if the
250 specified locale is different from the one in template1. (If it is not,
251 then specifying the locale explicitly is redundant.)
252 To create a database music2 with a different locale and a different
254 character set encoding:
255 CREATE DATABASE music2
257 LOCALE 'sv_SE.iso885915'
258 ENCODING LATIN9
259 TEMPLATE template0;
260 The specified locale and encoding settings must match, or an error will
262 be reported.
263 Note that locale names are specific to the operating system, so that
265 the above commands might not work in the same way everywhere.
266
268 There is no CREATE DATABASE statement in the SQL standard. Databases
269 are equivalent to catalogs, whose creation is implementation-defined.
270
272 ALTER DATABASE (ALTER_DATABASE(7)), DROP DATABASE (DROP_DATABASE(7))
273PostgreSQL 16.1 2023 CREATE DATABASE(7)