Updated on 2024-10-14 GMT+08:00

CREATE DATABASE

Function

CREATE DATABASE is used to create a database. By default, the new database will be created only by cloning the standard system database template0.

Precautions

  • A user that has the CREATEDB permission or a system administrator can create a database.
  • CREATE DATABASE cannot be executed inside a transaction block.
  • If an error message similar to "could not initialize database directory" is displayed during database creation, the possible cause is that the permission on the data directory in the file system is insufficient or the disk is full.

Syntax

1
2
3
4
5
6
7
8
9
CREATE DATABASE database_name
    [ [ WITH ] { [ OWNER [=] user_name ] |
               [ TEMPLATE [=] template ] |
               [ ENCODING [=] encoding ] |
               [ LC_COLLATE [=] lc_collate ] |
               [ LC_CTYPE [=] lc_ctype ] |
               [ DBCOMPATIBILITY [=] compatibilty_type ] |
               [ TABLESPACE [=] tablespace_name ] |
               [ CONNECTION LIMIT [=] connlimit ]}[...] ];

Parameter Description

  • database_name

    Specifies the database name.

    Value range: a string. It must comply with the naming convention.

  • OWNER [ = ] user_name

    Specifies the owner of the new database. If omitted, the default owner is the current user.

    Value range: an existing username

  • TEMPLATE [ = ] template

    Specifies a template name. That is, the template from which the database is created. GaussDB creates a database by copying data from a template database. GaussDB has two default template databases template0 and template1 and a default user database postgres.

    Value range: template0

  • ENCODING [ = ] encoding

    Specifies the character encoding used by the database. The value can be a string (for example, SQL_ASCII) or an integer.

    If this parameter is not specified, the encoding of the template database is used by default. By default, the codes of the template databases template0 and template1 are related to the operating system environment. The character encoding of template1 cannot be changed. To change the encoding, use template0 to create a database.

    Common values are GBK, UTF8, Latin1, and GB18030. The supported character sets are as follows:

    Table 1 Supported character sets

    Name

    Description

    Language

    Server-side Encoding

    ICU Support

    Number of Bytes/Characters

    Alias

    BIG5

    Big Five

    Traditional Chinese

    No

    No

    1–2

    WIN950, Windows950

    EUC_CN

    Extended UNIX Code-CN

    Simplified Chinese

    Yes

    Yes

    1–3

    -

    EUC_JP

    Extended UNIX Code-JP

    Japanese

    Yes

    Yes

    1–3

    -

    EUC_JIS_2004

    Extended UNIX Code-JP, JIS X 0213

    Japanese

    Yes

    No

    1–3

    -

    EUC_KR

    Extended UNIX Code-KR

    Korean

    Yes

    Yes

    1–3

    -

    EUC_TW

    Extended UNIX Code-Taiwan, China

    Traditional Chinese

    Yes

    Yes

    1–3

    -

    GB18030

    National standards

    Chinese

    Yes

    No

    1–4

    -

    GBK

    Extended national standards

    Simplified Chinese

    Yes

    No

    1–2

    WIN936, Windows936

    ISO_8859_5

    ISO 8859-5, ECMA 113

    Latin/Cyrillic

    Yes

    Yes

    1

    -

    ISO_8859_6

    ISO 8859-6, ECMA 114

    Latin/Arabic

    Yes

    Yes

    1

    -

    ISO_8859_7

    ISO 8859-7, ECMA 118

    Latin/Greek

    Yes

    Yes

    1

    -

    ISO_8859_8

    ISO 8859-8, ECMA 121

    Latin/Hebrew

    Yes

    Yes

    1

    -

    JOHAB

    JOHAB

    Korean

    No

    No

    1–3

    -

    KOI8R

    KOI8-R

    Cyrillic (Russian)

    Yes

    Yes

    1

    KOI8

    KOI8U

    KOI8-U

    Cyrillic (Ukrainian)

    Yes

    Yes

    1

    -

    LATIN1

    ISO 8859-1, ECMA 94

    Western European languages

    Yes

    Yes

    1

    ISO88591

    LATIN2

    ISO 8859-2, ECMA 94

    Central European languages

    Yes

    Yes

    1

    ISO88592

    LATIN3

    ISO 8859-3, ECMA 94

    South European languages

    Yes

    Yes

    1

    ISO88593

    LATIN4

    ISO 8859-4, ECMA 94

    North European languages

    Yes

    Yes

    1

    ISO88594

    LATIN5

    ISO 8859-9, ECMA 128

    Turkish

    Yes

    Yes

    1

    ISO88599

    LATIN6

    ISO 8859-10, ECMA 144

    Germanic languages

    Yes

    Yes

    1

    ISO885910

    LATIN7

    ISO 8859-13

    Baltic languages

    Yes

    Yes

    1

    ISO885913

    LATIN8

    ISO 8859-14

    Celtic languages

    Yes

    Yes

    1

    ISO885914

    LATIN9

    ISO 8859-15

    LATIN1 with Euro and accents

    Yes

    Yes

    1

    ISO885915

    LATIN10

    ISO 8859-16, ASRO SR 14111

    Romanian

    Yes

    No

    1

    ISO885916

    MULE_INTERNAL

    Mule internal code

    Multilingual Emacs

    Yes

    No

    1–4

    -

    SJIS

    Shift JIS

    Japanese

    No

    No

    1–2

    Mskanji, ShiftJIS, WIN932, Windows932

    SHIFT_JIS_2004

    Shift JIS, JIS X 0213

    Japanese

    No

    No

    1–2

    -

    SQL_ASCII

    Unspecified (see the text)

    Any

    Yes

    No

    1

    -

    UHC

    Unified Hangul Code

    Korean

    No

    No

    1–2

    WIN949, Windows949

    UTF8

    Unicode, 8-bit

    All

    Yes

    Yes

    1–4

    Unicode

    WIN866

    Windows CP866

    Cyrillic

    Yes

    Yes

    1

    ALT

    WIN874

    Windows CP874

    Thai

    Yes

    No

    1

    -

    WIN1250

    Windows CP1250

    Central European languages

    Yes

    Yes

    1

    -

    WIN1251

    Windows CP1251

    Cyrillic

    Yes

    Yes

    1

    WIN

    WIN1252

    Windows CP1252

    Western European languages

    Yes

    Yes

    1

    -

    WIN1253

    Windows CP1253

    Greek

    Yes

    Yes

    1

    -

    WIN1254

    Windows CP1254

    Turkish

    Yes

    Yes

    1

    -

    WIN1255

    Windows CP1255

    Hebrew

    Yes

    Yes

    1

    -

    WIN1256

    Windows CP1256

    Arabic

    Yes

    Yes

    1

    -

    WIN1257

    Windows CP1257

    Baltic languages

    Yes

    Yes

    1

    -

    WIN1258

    Windows CP1258

    Vietnamese

    Yes

    Yes

    1

    ABC, TCVN, TCVN5712, VSCII

    Note that not all client APIs support the preceding character sets.

    The SQL_ASCII setting performs quite differently from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If this parameter is set to SQL_ASCII, no code conversion occurs. Therefore, this setting is not basically used to declare the specified encoding used, because this declaration ignores the encoding. In most cases, if you use any non-ASCII data, it is unwise to use the SQL_ASCII setting because the database will not be able to help you convert or verify non-ASCII characters.

    • The character set encoding of the new database must be compatible with the local settings (LC_COLLATE and LC_CTYPE).
    • When the specified character encoding set is GBK, some uncommon Chinese characters cannot be directly used as object names. This is because the byte encoding overlaps with the ASCII characters @A-Z[\]^_`a-z{|} when the second byte of the GBK ranges from 0x40 to 0x7E. @[\]^_'{|} is an operator in the database. If it is directly used as an object name, a syntax error will be reported. For example, the GBK hexadecimal code is 0x8240, and the second byte is 0x40, which is the same as the ASCII character @. Therefore, the character cannot be used as an object name. If you do need to use this function, you can add double quotation marks ("") to avoid this problem when creating and accessing objects.
  • LC_COLLATE [ = ] lc_collate

    Specifies the character set used by the new database. For example, set this parameter by using lc_collate = 'zh_CN.gbk'.

    The use of this parameter affects the sort order of strings (for example, the order of using ORDER BY for execution and the order of using indexes on text columns). By default, the sorting order of the template database is used.

    Value range: character sets supported by the OS.

  • LC_CTYPE [ = ] lc_ctype

    Specifies the character class used by the new database. For example, set this parameter by using lc_ctype = 'zh_CN.gbk'. The use of this parameter affects the classification of characters, such as uppercase letters, lowercase letters, and digits. By default, the character classification of the template database is used.

    Value range: character classes supported by the OS.

    The value ranges of lc_collate and lc_ctype depend on the character sets supported by the local environment. For example, in the Linux operating system, you can run the locale -a command to obtain the list of character sets supported by the operating system. When using the lc_collate and lc_ctype parameters, you can select the required character sets and character classes.

  • DBCOMPATIBILITY [ = ] compatibilty_type

    Specifies the compatible database type. The default value is MySQL.

    Value range: MYSQL, TD, ORA, and PG. MySQL, Teradata, Oracle and PostgreSQL are compatible, respectively.

    • For ORA compatibility, the database treats empty strings as NULL and replaces DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
    • When a character string is converted to an integer, if the input is invalid, the input will be converted to 0 due to MYSQL compatibility, and an error will be reported due to other compatibility issues.
    • For PG compatibility, CHAR and VARCHAR are counted by character. For other compatibility types, they are counted by byte. For example, for the UTF-8 character set, CHAR(3) can store three Chinese characters in PG compatibility scenarios, but can store only one Chinese character in other compatibility scenarios.
  • TABLESPACE [ = ] tablespace_name

    Specifies the tablespace of the database.

    Value range: an existing tablespace name

  • CONNECTION LIMIT [ = ] connlimit

    Specifies the maximum number of concurrent connections that can be made to the new database.

    • The system administrator is not restricted by this parameter.
    • connlimit is calculated for each CN. The number of connections in a cluster is calculated using the following formula: Number of connections in a cluster = connlimit x Number of normal CNs.

    Value range: an integer greater than or equal to -1 The default value is -1, indicating that there is no limit.

The restrictions on character encoding are as follows:

  • If the locale is set to C (or POSIX), all encoding types are allowed. For other locale settings, the character encoding must be the same as that of the locale.
  • The encoding and region settings must match the template database, except that template0 is used as a template. This is because other databases may contain data that does not match the specified encoding, or may contain indexes whose sorting order is affected by LC_COLLATE and LC_CTYPE. Copying this data will invalidate the indexes in the new database. template0 does not contain any data or indexes that may be affected.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Create users jim and tom.
openGauss=# CREATE USER jim PASSWORD 'xxxxxxxxxx';
openGauss=# CREATE USER tom PASSWORD 'xxxxxxxxxx';

-- Create database music using GBK (the local encoding type is also GBK).
openGauss=# CREATE DATABASE music ENCODING 'GBK' template = template0;

-- Create database music2 and specify user jim as its owner.
openGauss=# CREATE DATABASE music2 OWNER jim;

-- Create database music3 using template template0 and specify user jim as its owner.
openGauss=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;

-- Set the maximum number of connections to database music to 10.
openGauss=# ALTER DATABASE music CONNECTION LIMIT= 10;

-- Rename database music to music4.
openGauss=# ALTER DATABASE music RENAME TO music4;

-- Change the owner of database music2 to user tom.
openGauss=# ALTER DATABASE music2 OWNER TO tom;

-- Delete the database.
openGauss=# DROP DATABASE music2;
openGauss=# DROP DATABASE music3;
openGauss=# DROP DATABASE music4;

-- Delete the jim and tom users.
openGauss=# DROP USER jim;
openGauss=# DROP USER tom;

-- Create a database compatible with the TD format.
openGauss=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'TD';

-- Create a database compatible with the ORA format.
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';

-- Delete the databases that are compatible with the TD and ORA formats.
openGauss=# DROP DATABASE td_compatible_db;
openGauss=# DROP DATABASE ora_compatible_db;

Helpful Links

ALTER DATABASE and DROP DATABASE

Suggestions

  • create database

    Database cannot be created in a transaction.

  • ENCODING

    If the new database Encoding does not match the template database (SQL_ASCII) ('GBK', 'UTF8', 'LATIN1', or 'GB18030'), template [=] template0 must be specified.