Postgresql 中文操作指南

CREATE DATABASE

CREATE DATABASE — 创建新数据库

CREATE DATABASE — create a new database

Synopsis

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]
           [ ICU_RULES [=] icu_rules ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

Description

CREATE DATABASE 创建新的 PostgreSQL 数据库。

CREATE DATABASE creates a new PostgreSQL database.

要创建数据库，您必须是超级用户或拥有特殊的 CREATEDB 权限。参见 CREATE ROLE 。

To create a database, you must be a superuser or have the special CREATEDB privilege. See CREATE ROLE.

默认情况下，将通过克隆标准系统数据库 template1 来创建新数据库。可以通过写 TEMPLATE _name_ 来指定不同的模板。特别地，通过写 TEMPLATE template0 ，您可以创建原始数据库（其中不存在用户定义的对象，系统对象也未被更改），它仅包含 PostgreSQL 版本预定义的标准对象。如果您希望避免复制可能添加到 template1 的任何安装本地对象，这将非常有用。

By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE _name_. In particular, by writing TEMPLATE template0, 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 PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

Parameters

  • name #

    • The name of a database to create.

  • user_name #

    • The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be able to SET ROLE to that role.

  • template #

    • The name of the template from which to create the new database, or DEFAULT to use the default template (template1).

  • encoding #

    • Character set encoding to use in the new database. Specify a string constant (e.g., 'SQL_ASCII'), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). The character sets supported by the PostgreSQL server are described in Section 24.3.1. See below for additional restrictions.

  • strategy #

    • Strategy to be used in creating the new database. If the WAL_LOG 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 FILE_COPY 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.

  • locale #

    • Sets the default collation order and character classification in the new database. Collation 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. Character classification affects the categorization of characters, e.g., lower, upper, and digit. Also sets the associated aspects of the operating system environment, LC_COLLATE and LC_CTYPE. The default is the same setting as the template database. See Section 24.2.2.3.1 and Section 24.2.2.3.2 for details.

    • Can be overridden by setting lc_collate, lc_ctype, or icu_locale individually.

  • lc_collate #

    • Sets LC_COLLATE in the database server’s operating system environment. The default is the setting of locale if specified, otherwise the same setting as the template database. See below for additional restrictions.

    • If locale_provider is libc, also sets the default collation order to use in the new database, overriding the setting locale.

  • lc_ctype #

    • Sets LC_CTYPE in the database server’s operating system environment. The default is the setting of locale if specified, otherwise the same setting as the template database. See below for additional restrictions.

    • If locale_provider is libc, also sets the default character classification to use in the new database, overriding the setting locale.

  • icu_locale #

    • Specifies the ICU locale (see Section 24.2.2.3.2) for the database default collation order and character classification, overriding the setting locale. The locale provider must be ICU. The default is the setting of locale if specified; otherwise the same setting as the template database.

  • icu_rules #

    • Specifies additional collation rules to customize the behavior of the default collation of this database. This is supported for ICU only. See Section 24.2.3.4 for details.

  • locale_provider #

    • Specifies the provider to use for the default collation in this database. Possible values are icu (if the server was built with ICU support) or libc. By default, the provider is the same as that of the template. See Section 24.1.4 for details.

  • collation_version #

    • 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 pg_upgrade for copying the version from an existing installation.

    • See also ALTER DATABASE for how to handle database collation version mismatches.

  • tablespace_name #

    • The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information.

  • allowconn #

    • If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT/REVOKE CONNECT).

  • connlimit #

    • How many concurrent connections can be made to this database. -1 (the default) means no limit.

  • istemplate #

    • If true, then this database can be cloned by any user with CREATEDB privileges; if false (the default), then only superusers or the owner of the database can clone it.

  • oid #

    • The object identifier to be used for the new database. If this parameter is not specified, PostgreSQL will choose a suitable OID automatically. This parameter is primarily intended for internal use by pg_upgrade, and only pg_upgrade can specify a value less than 16384.

Tip

其他语言环境设置 lc_messages 、 lc_monetary 、 lc_numeric 和 lc_time 不会因数据库而变动，并且不会由此命令设置。如果你想使其成为特定数据库的默认设置，则可以使用 ALTER DATABASE …​ SET 。

The other locale settings lc_messages, lc_monetary, lc_numeric, and lc_time 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 ALTER DATABASE …​ SET.

可选参数可以按任何顺序编写，而不仅仅是上述说明中的顺序。

Optional parameters can be written in any order, not only the order illustrated above.

Notes

不能在事务块中执行 CREATE DATABASE 。

CREATE DATABASE cannot be executed inside a transaction block.

“无法初始化数据库目录”之类的错误最可能与数据目录上的权限不足、磁盘已满或其他文件系统问题有关。

Errors along the line of “could not initialize database directory” are most likely related to insufficient permissions on the data directory, a full disk, or other file system problems.

使用 DROP DATABASE 删除数据库。

Use DROP DATABASE to remove a database.

程序 createdb 是此命令的包装程序，为方便起见而提供。

The program createdb is a wrapper program around this command, provided for convenience.

数据库级别配置参数（通过 ALTER DATABASE 设置）和数据库级别权限（通过 GRANT 设置）不会从模板数据库中复制。

Database-level configuration parameters (set via ALTER DATABASE) and database-level permissions (set via GRANT) are not copied from the template database.

虽然可以通过将数据库名称指定为模板来复制 template1 之外的其他数据库，但此举（目前）不打算作为通用“ COPY DATABASE ”设施。主要限制在于在复制模板数据库时，没有其他会话可以连接到该数据库。如果在启动 CREATE DATABASE 时有任何其他连接，它将失败；否则，在 CREATE DATABASE 完成之前，不会建立新的连接到该模板数据库。请参阅 Section 23.3 了解详情。

Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 23.3 for more information.

为新数据库指定的字符集编码必须与所选择的语言环境设置（ LC_COLLATE 和 LC_CTYPE ）兼容。如果语言环境是 C （或同等的 POSIX ），则允许所有编码，但是对于其他语言环境设置，只有一项编码能够正常工作。（但是，在 Windows 上，UTF-8 编码可用于任何语言环境。） CREATE DATABASE 允许超级用户指定 SQL_ASCII 编码，而不考虑语言环境设置，但此选择已弃用，如果在数据库中存储与语言环境不兼容的编码数据，则可能会导致字符字符串函数出现错误。

The character set encoding specified for the new database must be compatible with the chosen locale settings (LC_COLLATE and LC_CTYPE). If the locale is C (or equivalently POSIX), 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.) CREATE DATABASE will allow superusers to specify SQL_ASCII 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.

编码和语言环境设置必须与模板数据库的设置相匹配，但将 template0 用作模板除外。这是因为其他数据库可能包含与指定编码不匹配的数据，或可能包含排序顺序受 LC_COLLATE 和 LC_CTYPE 影响的索引。复制此类数据将导致数据库根据新设置损坏。然而，众所周知， template0 不包含任何数据或索引，而这些数据或索引会受到影响。

The encoding and locale settings must match those of the template database, except when template0 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 LC_COLLATE and LC_CTYPE. Copying such data would result in a database that is corrupt according to the new settings. template0, however, is known to not contain any data or indexes that would be affected.

目前，没有使用具有非确定性比较的数据库语言环境的选项（参阅 CREATE COLLATION 了解解释）。如果有此需要，则需要使用基于列的整理规则。

There is currently no option to use a database locale with nondeterministic comparisons (see CREATE COLLATION for an explanation). If this is needed, then per-column collations would need to be used.

CONNECTION LIMIT 选项只近似强制执行；如果两个新会话在数据库只剩下一个连接“插槽”的时候同时启动，则两者都可能失败。此外，限制不会对超级用户或后台工作进程强制执行。

The CONNECTION LIMIT option is only enforced approximately; if two new sessions start at about the same time when just one connection “slot” remains for the database, it is possible that both will fail. Also, the limit is not enforced against superusers or background worker processes.

Examples

创建新数据库：

To create a new database:

CREATE DATABASE lusiadas;

要创建由用户 salesapp 拥有的数据库 sales ，其默认表空间为 salesspace ：

To create a database sales owned by user salesapp with a default tablespace of salesspace:

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

要创建具有不同语言环境的数据库 music ：

To create a database music with a different locale:

CREATE DATABASE music
    LOCALE 'sv_SE.utf8'
    TEMPLATE template0;

在此示例中，如果指定的语言环境不同于 template1 中的语言环境，则 TEMPLATE template0 从句必需。（如果不同，则显式指定语言环境是多余的。）

In this example, the TEMPLATE template0 clause is required if the specified locale is different from the one in template1. (If it is not, then specifying the locale explicitly is redundant.)

要创建具有不同语言环境和不同字符集编码的数据库 music2 ：

To create a database music2 with a different locale and a different character set encoding:

CREATE DATABASE music2
    LOCALE 'sv_SE.iso885915'
    ENCODING LATIN9
    TEMPLATE template0;

指定的语言环境和编码设置必须匹配，否则将报告错误。

The specified locale and encoding settings must match, or an error will be reported.

请注意，语言环境名称特定于操作系统，因此以上命令可能无法在所有地方以相同方式运行。

Note that locale names are specific to the operating system, so that the above commands might not work in the same way everywhere.

Compatibility

SQL 标准中没有 CREATE DATABASE 语句。数据库等效于目录，其创建由实现定义。

There is no CREATE DATABASE statement in the SQL standard. Databases are equivalent to catalogs, whose creation is implementation-defined.

See Also

ALTER DATABASE 、 DROP DATABASE

ALTER DATABASE, DROP DATABASE