summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createdatabase.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
commit311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/sql-createdatabase.html
parentInitial commit. (diff)
downloadpostgresql-15-upstream.tar.xz
postgresql-15-upstream.zip
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-createdatabase.html')
-rw-r--r--doc/src/sgml/html/sql-createdatabase.html240
1 files changed, 240 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createdatabase.html b/doc/src/sgml/html/sql-createdatabase.html
new file mode 100644
index 0000000..e22840b
--- /dev/null
+++ b/doc/src/sgml/html/sql-createdatabase.html
@@ -0,0 +1,240 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE DATABASE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createconversion.html" title="CREATE CONVERSION" /><link rel="next" href="sql-createdomain.html" title="CREATE DOMAIN" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE DATABASE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createconversion.html" title="CREATE CONVERSION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createdomain.html" title="CREATE DOMAIN">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEDATABASE"><div class="titlepage"></div><a id="id-1.9.3.61.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE DATABASE</span></h2><p>CREATE DATABASE — create a new database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE DATABASE <em class="replaceable"><code>name</code></em>
+ [ WITH ] [ OWNER [=] <em class="replaceable"><code>user_name</code></em> ]
+ [ TEMPLATE [=] <em class="replaceable"><code>template</code></em> ]
+ [ ENCODING [=] <em class="replaceable"><code>encoding</code></em> ]
+ [ STRATEGY [=] <em class="replaceable"><code>strategy</code></em> ] ]
+ [ LOCALE [=] <em class="replaceable"><code>locale</code></em> ]
+ [ LC_COLLATE [=] <em class="replaceable"><code>lc_collate</code></em> ]
+ [ LC_CTYPE [=] <em class="replaceable"><code>lc_ctype</code></em> ]
+ [ ICU_LOCALE [=] <em class="replaceable"><code>icu_locale</code></em> ]
+ [ LOCALE_PROVIDER [=] <em class="replaceable"><code>locale_provider</code></em> ]
+ [ COLLATION_VERSION = <em class="replaceable"><code>collation_version</code></em> ]
+ [ TABLESPACE [=] <em class="replaceable"><code>tablespace_name</code></em> ]
+ [ ALLOW_CONNECTIONS [=] <em class="replaceable"><code>allowconn</code></em> ]
+ [ CONNECTION LIMIT [=] <em class="replaceable"><code>connlimit</code></em> ]
+ [ IS_TEMPLATE [=] <em class="replaceable"><code>istemplate</code></em> ]
+ [ OID [=] <em class="replaceable"><code>oid</code></em> ]
+</pre></div><div class="refsect1" id="id-1.9.3.61.5"><h2>Description</h2><p>
+ <code class="command">CREATE DATABASE</code> creates a new
+ <span class="productname">PostgreSQL</span> database.
+ </p><p>
+ To create a database, you must be a superuser or have the special
+ <code class="literal">CREATEDB</code> privilege.
+ See <a class="xref" href="sql-createrole.html" title="CREATE ROLE"><span class="refentrytitle">CREATE ROLE</span></a>.
+ </p><p>
+ By default, the new database will be created by cloning the standard
+ system database <code class="literal">template1</code>. A different template can be
+ specified by writing <code class="literal">TEMPLATE
+ <em class="replaceable"><code>name</code></em></code>. In particular,
+ by writing <code class="literal">TEMPLATE template0</code>, you can create a pristine
+ database (one where no user-defined objects exist and where the system
+ objects have not been altered)
+ containing only the standard objects predefined by your
+ version of <span class="productname">PostgreSQL</span>. This is useful
+ if you wish to avoid copying
+ any installation-local objects that might have been added to
+ <code class="literal">template1</code>.
+ </p></div><div class="refsect1" id="id-1.9.3.61.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
+ The name of a database to create.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>user_name</code></em></span></dt><dd><p>
+ The role name of the user who will own the new database,
+ or <code class="literal">DEFAULT</code> to use the default (namely, the
+ 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.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>template</code></em></span></dt><dd><p>
+ The name of the template from which to create the new database,
+ or <code class="literal">DEFAULT</code> to use the default template
+ (<code class="literal">template1</code>).
+ </p></dd><dt><span class="term"><em class="replaceable"><code>encoding</code></em></span></dt><dd><p>
+ Character set encoding to use in the new database. Specify
+ a string constant (e.g., <code class="literal">'SQL_ASCII'</code>),
+ or an integer encoding number, or <code class="literal">DEFAULT</code>
+ to use the default encoding (namely, the encoding of the
+ template database). The character sets supported by the
+ <span class="productname">PostgreSQL</span> server are described in
+ <a class="xref" href="multibyte.html#MULTIBYTE-CHARSET-SUPPORTED" title="24.3.1. Supported Character Sets">Section 24.3.1</a>. See below for
+ additional restrictions.
+ </p></dd><dt id="CREATE-DATABASE-STRATEGY"><span class="term"><em class="replaceable"><code>strategy</code></em></span></dt><dd><p>
+ Strategy to be used in creating the new database. If
+ the <code class="literal">WAL_LOG</code> strategy is used, the database will be
+ copied block by block and each block will be separately written
+ to the write-ahead log. This is the most efficient strategy in
+ cases where the template database is small, and therefore it is the
+ default. The older <code class="literal">FILE_COPY</code> strategy is also
+ available. This strategy writes a small record to the write-ahead log
+ for each tablespace used by the target database. Each such record
+ represents copying an entire directory to a new location at the
+ filesystem level. While this does reduce the write-ahead
+ log volume substantially, especially if the template database is large,
+ it also forces the system to perform a checkpoint both before and
+ after the creation of the new database. In some situations, this may
+ have a noticeable negative impact on overall system performance.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>locale</code></em></span></dt><dd><p>
+ This is a shortcut for setting <code class="symbol">LC_COLLATE</code>
+ and <code class="symbol">LC_CTYPE</code> at once.
+ </p><div class="tip"><h3 class="title">Tip</h3><p>
+ The other locale settings <a class="xref" href="runtime-config-client.html#GUC-LC-MESSAGES">lc_messages</a>, <a class="xref" href="runtime-config-client.html#GUC-LC-MONETARY">lc_monetary</a>, <a class="xref" href="runtime-config-client.html#GUC-LC-NUMERIC">lc_numeric</a>, and
+ <a class="xref" href="runtime-config-client.html#GUC-LC-TIME">lc_time</a> are not fixed per database and are not
+ set by this command. If you want to make them the default for a
+ specific database, you can use <code class="literal">ALTER DATABASE
+ ... SET</code>.
+ </p></div></dd><dt><span class="term"><em class="replaceable"><code>lc_collate</code></em></span></dt><dd><p>
+ Collation order (<code class="literal">LC_COLLATE</code>) 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.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>lc_ctype</code></em></span></dt><dd><p>
+ Character classification (<code class="literal">LC_CTYPE</code>) 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.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>icu_locale</code></em></span></dt><dd><p>
+ Specifies the ICU locale ID if the ICU locale provider is used.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>locale_provider</code></em></span></dt><dd><p>
+ Specifies the provider to use for the default collation in this
+ database. Possible values are:
+ <code class="literal">icu</code>,<a id="id-1.9.3.61.6.2.10.2.1.2" class="indexterm"></a>
+ <code class="literal">libc</code>. <code class="literal">libc</code> is the default. The
+ available choices depend on the operating system and build options.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>collation_version</code></em></span></dt><dd><p>
+ Specifies the collation version string to store with the database.
+ Normally, this should be omitted, which will cause the version to be
+ computed from the actual version of the database collation as provided
+ by the operating system. This option is intended to be used by
+ <code class="command">pg_upgrade</code> for copying the version from an existing
+ installation.
+ </p><p>
+ See also <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a> for how to handle
+ database collation version mismatches.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>tablespace_name</code></em></span></dt><dd><p>
+ The name of the tablespace that will be associated with the
+ new database, or <code class="literal">DEFAULT</code> to use the
+ template database's tablespace. This
+ tablespace will be the default tablespace used for objects
+ created in this database. See
+ <a class="xref" href="sql-createtablespace.html" title="CREATE TABLESPACE"><span class="refentrytitle">CREATE TABLESPACE</span></a>
+ for more information.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>allowconn</code></em></span></dt><dd><p>
+ If false then no one can connect to this database. The default is
+ true, allowing connections (except as restricted by other mechanisms,
+ such as <code class="literal">GRANT</code>/<code class="literal">REVOKE CONNECT</code>).
+ </p></dd><dt><span class="term"><em class="replaceable"><code>connlimit</code></em></span></dt><dd><p>
+ How many concurrent connections can be made
+ to this database. -1 (the default) means no limit.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>istemplate</code></em></span></dt><dd><p>
+ If true, then this database can be cloned by any user with <code class="literal">CREATEDB</code>
+ privileges; if false (the default), then only superusers or the owner
+ of the database can clone it.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>oid</code></em></span></dt><dd><p>
+ The object identifier to be used for the new database. If this
+ parameter is not specified, <span class="productname">PostgreSQL</span>
+ will choose a suitable OID automatically. This parameter is primarily
+ intended for internal use by <span class="application">pg_upgrade</span>,
+ and only <span class="application">pg_upgrade</span> can specify a value
+ less than 16384.
+ </p></dd></dl></div><p>
+ Optional parameters can be written in any order, not only the order
+ illustrated above.
+ </p></div><div class="refsect1" id="id-1.9.3.61.7"><h2>Notes</h2><p>
+ <code class="command">CREATE DATABASE</code> cannot be executed inside a transaction
+ block.
+ </p><p>
+ Errors along the line of <span class="quote">“<span class="quote">could not initialize database directory</span>”</span>
+ are most likely related to insufficient permissions on the data
+ directory, a full disk, or other file system problems.
+ </p><p>
+ Use <a class="link" href="sql-dropdatabase.html" title="DROP DATABASE"><code class="command">DROP DATABASE</code></a> to remove a database.
+ </p><p>
+ The program <a class="xref" href="app-createdb.html" title="createdb"><span class="refentrytitle"><span class="application">createdb</span></span></a> is a
+ wrapper program around this command, provided for convenience.
+ </p><p>
+ Database-level configuration parameters (set via <a class="link" href="sql-alterdatabase.html" title="ALTER DATABASE"><code class="command">ALTER DATABASE</code></a>) and database-level permissions (set via
+ <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a>) are not copied from the template database.
+ </p><p>
+ Although it is possible to copy a database other than <code class="literal">template1</code>
+ by specifying its name as the template, this is not (yet) intended as
+ a general-purpose <span class="quote">“<span class="quote"><code class="command">COPY DATABASE</code></span>”</span> facility.
+ The principal limitation is that no other sessions can be connected to
+ the template database while it is being copied. <code class="command">CREATE
+ DATABASE</code> will fail if any other connection exists when it starts;
+ otherwise, new connections to the template database are locked out
+ until <code class="command">CREATE DATABASE</code> completes.
+ See <a class="xref" href="manage-ag-templatedbs.html" title="23.3. Template Databases">Section 23.3</a> for more information.
+ </p><p>
+ The character set encoding specified for the new database must be
+ compatible with the chosen locale settings (<code class="literal">LC_COLLATE</code> and
+ <code class="literal">LC_CTYPE</code>). If the locale is <code class="literal">C</code> (or equivalently
+ <code class="literal">POSIX</code>), 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.)
+ <code class="command">CREATE DATABASE</code> will allow superusers to specify
+ <code class="literal">SQL_ASCII</code> 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.
+ </p><p>
+ The encoding and locale settings must match those of the template database,
+ except when <code class="literal">template0</code> 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
+ <code class="literal">LC_COLLATE</code> and <code class="literal">LC_CTYPE</code>. Copying such data would
+ result in a database that is corrupt according to the new settings.
+ <code class="literal">template0</code>, however, is known to not contain any data or
+ indexes that would be affected.
+ </p><p>
+ There is currently no option to use a database locale with nondeterministic
+ comparisons (see <a class="link" href="sql-createcollation.html" title="CREATE COLLATION"><code class="command">CREATE
+ COLLATION</code></a> for an explanation). If this is needed, then
+ per-column collations would need to be used.
+ </p><p>
+ The <code class="literal">CONNECTION LIMIT</code> option is only enforced approximately;
+ if two new sessions start at about the same time when just one
+ connection <span class="quote">“<span class="quote">slot</span>”</span> remains for the database, it is possible that
+ both will fail. Also, the limit is not enforced against superusers or
+ background worker processes.
+ </p></div><div class="refsect1" id="id-1.9.3.61.8"><h2>Examples</h2><p>
+ To create a new database:
+
+</p><pre class="programlisting">
+CREATE DATABASE lusiadas;
+</pre><p>
+ </p><p>
+ To create a database <code class="literal">sales</code> owned by user <code class="literal">salesapp</code>
+ with a default tablespace of <code class="literal">salesspace</code>:
+
+</p><pre class="programlisting">
+CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
+</pre><p>
+ </p><p>
+ To create a database <code class="literal">music</code> with a different locale:
+</p><pre class="programlisting">
+CREATE DATABASE music
+ LOCALE 'sv_SE.utf8'
+ TEMPLATE template0;
+</pre><p>
+ In this example, the <code class="literal">TEMPLATE template0</code> clause is required if
+ the specified locale is different from the one in <code class="literal">template1</code>.
+ (If it is not, then specifying the locale explicitly is redundant.)
+ </p><p>
+ To create a database <code class="literal">music2</code> with a different locale and a
+ different character set encoding:
+</p><pre class="programlisting">
+CREATE DATABASE music2
+ LOCALE 'sv_SE.iso885915'
+ ENCODING LATIN9
+ TEMPLATE template0;
+</pre><p>
+ The specified locale and encoding settings must match, or an error will be
+ reported.
+ </p><p>
+ Note that locale names are specific to the operating system, so that the
+ above commands might not work in the same way everywhere.
+ </p></div><div class="refsect1" id="id-1.9.3.61.9"><h2>Compatibility</h2><p>
+ There is no <code class="command">CREATE DATABASE</code> statement in the SQL
+ standard. Databases are equivalent to catalogs, whose creation is
+ implementation-defined.
+ </p></div><div class="refsect1" id="id-1.9.3.61.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a>, <a class="xref" href="sql-dropdatabase.html" title="DROP DATABASE"><span class="refentrytitle">DROP DATABASE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createconversion.html" title="CREATE CONVERSION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createdomain.html" title="CREATE DOMAIN">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE CONVERSION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE DOMAIN</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-01 04:11:29 +0000