CREATE DATABASE

Create a new database.

Summary

CREATE DATABASE name [ [WITH] [OWNER [=] user_name]
                [TEMPLATE [=] template]
                [ENCODING [=] encoding]
                [LC_COLLATE [=] lc_collate]
                [LC_CTYPE [=] lc_ctype]
                [TABLESPACE [=] tablespace]
                [CONNECTION LIMIT [=] connlimit ] ]

describe

CREATE DATABASE creates a new database. To create a database, you must be a superuser or have special CREATEDB privileges.

By default, the creator becomes the owner of the new database. Superusers can use the OWNER clause to create databases owned by other users. They can even create databases owned by users without special privileges. Non-super users with CREATEDB privileges can only create databases that they own.

By default, a new database will be created by cloning the standard system database template1. Other templates can be specified by writing to the TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a clean database that contains only standard objects predefined by the YMatrix database. This feature is useful if you want to avoid copying any local installation objects that may have been added to template1.

Parameters

name

The name of the database to be created.

user_name

Name of the user who owns the new database, or use the default owner (user who executes the command) DEFAULT.

template

Create the name of the template for the new database, or use the template for the default template (template1).

encoding

Character set encoding used in the new database. Specify string constants (such as 'SQL_ASCII'), integer encoding numbers, or DEFAULT to use default encoding. For more information, see Character Set Support.

lc_collate

The collation (LC_COLLATE) used in the new database. This affects the order in which the strings are applied, such as in queries that use ORDER BY, and in indexes of text columns. The default setting is to use the collation of the template database. See the Comments section for additional restrictions.

lc_ctype

Character classification (LC_CTYPE) used in the new database. This affects the classification of characters, such as below, upper and digits. The default value is to use the character classification of the template database. See below for additional restrictions.

tablespace

The name of the tablespace that will be associated with the new database, or the tablespace of the template database is DEFAULT. This tablespace will be the default tablespace for objects created in this database.

connlimit

The maximum number of concurrent connections possible. The default value -1 means there is no limit.

Note

CREATE DATABASE cannot be executed within a transaction block.

When you replicate a database by specifying the database name as a template, other sessions cannot be connected to the template database during the replication process. The new connection to the template database is locked until CREATE DATABASE is completed.

There is no mandatory CONNECTION LIMIT for superusers.

The character set encoding specified for the new database must be compatible with the selected locale settings (LC_COLLATE and LC_CTYPE). If the locale is C (or equivalent POSIX), all encodings are allowed, but for other locale settings, only one encoding works fine. CREATE DATABASE will allow superusers to specify SQL_ASCII encoding regardless of locale settings, but this option is not recommended and may cause an exception to the string function if locale is not compatible with the locale.

The encoding and locale settings must match the settings of the template database unless template0 is used as the template. This is because COLLATE and CTYPE affect the order of indexes, so any index copied from the template database will be invalid in a new database with different settings. However, template0 is known to contain no data or indexes that will be affected.

Example

To create a new database:

CREATE DATABASE test;

To create a database sales owned by the user salesapp and use the default tablespace of salesspace:

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

To create a database music that supports the ISO-8859-1 character set:

CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;

In this example, the TEMPLATE template0 clause is only required if the encoding of template1 is not ISO-8859-1. Note that changing the encoding may also require selecting new LC_COLLATE and LC_CTYPE settings.

compatibility

There is no CREATE DATABASE statement in the SQL standard. A database is equivalent to a directory, and the creation of a directory is implemented to define it.