CREATE DATABASE
Description
Creates a database. By default, the new database is created by copying template0 of the standard system database.
Precautions
- A user who has the CREATEDB permission or a system administrator can create a database.
- It 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.
- templatem cannot be used as the name of the new database during database creation.
- The database creation operation is executed sequentially on each node. If a remote node fails to execute the operation, the error message "CONTEXT: Error message received from nodes: node name "is displayed.
Syntax
1 2 3 4 5 6 7 8 9 10 |
CREATE DATABASE database_name [ [ WITH ] { [ OWNER [=] user_name ] | [ TEMPLATE [=] template ] | [ ENCODING [=] 'encoding' ] | [ LC_COLLATE [=] 'lc_collate' ] | [ LC_CTYPE [=] 'lc_ctype' ] | [ DBCOMPATIBILITY [=] 'compatibility_type' ] | [ TABLESPACE [=] tablespace_name ] | [ CONNECTION LIMIT [=] connlimit ] | [ DBTIMEZONE [=] 'time_zone' ] }[...] ]; |
Parameters
- database_name
Specifies the database name.
Value range: a string. It must comply with Identifier Naming Conventions and contain a maximum of 63 characters.
If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the database name.
If a database name contains uppercase letters, the database automatically converts the uppercase letters into lowercase letters. To create a database name that contains uppercase letters, enclose the database name with double quotation marks ("").
- OWNER [ = ] user_name
(Optional) Specifies the owner of the new database.
Value range: an existing username. If no username is specified, the owner of the new database is the current user.
- TEMPLATE [ = ] template
(Optional) Creates a database based on the specified standard system database template.
GaussDB creates a database by copying data from a template database. GaussDB has four default template databases template0, template1, templatea, and templatem, and a default user database postgres.
Value range: template0, templatem, and templatea.
- ENCODING [ = ] 'encoding'
(Optional) Specifies the character encoding used by the database.
If this parameter is not specified, the M-compatible database uses the UTF-8 encoding by default, and other compatible databases use the encoding of the template database by default.
By default, the encoding of the template databases template0, template1, and templatea is related to the OS environment.
The character encoding of template1 cannot be changed. To modify the character encoding, use the template0 or templatea database to create a database.
Common values are GBK, UTF8, Latin1, and GB18030. The supported character sets are listed in Table 1.
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
-
GB18030_2022
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
ZHS16GBK
Extended national standards
Simplified Chinese
Yes
No
1–2
-
- Not all client APIs support the character sets listed in Table 1.
- The behavior of the SQL_ASCII setting is different from that of 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 characters that cannot be parsed. If any non-ASCII data is used, do not set the character set to SQL_ASCII. If the character set is set to SQL_ASCII, the database cannot 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 or ZHS16GBK, some uncommon Chinese characters cannot be directly used as object names. This is because when the second byte of the GBK ranges from 0x40 to 0x7E, the byte encoding overlaps with the ASCII characters, including uppercase characters (A–Z), lowercase characters (a–z), @, [, \, ], ^, _, `, {, |, and }. @, [, \, ], ^, _, `, {, |, and } are operators in the database. If they are directly used as object names, a syntax error will be reported.
- If the encoding format on the client is different from that on the server, the database must support encoding format conversion. You can view all supported encoding formats in the PG_CONVERSION system catalog. If the database does not support encoding format conversion, it is recommended that the client-side encoding be the same as the server-side encoding. You can change the client-side encoding by setting the GUC parameter client_encoding.
- If both the database character set encoding and client-side encoding are GB18030_2022, ensure that the client OS supports GB18030_2022. Otherwise, data inconsistency may occur due to incomplete compatibility between GB18030 character set versions. In addition, if historical data needs to be switched to the GB18030_2022 database, follow the database switchover process to migrate data.
- LC_COLLATE [ = ] 'lc_collate'
(Optional) Specifies the character set used by the new database.
Value range: character set supported by the OS. By default, the character set of the template database is used.
- The use of this parameter affects the sorting order of strings, such as the sorting of the ORDER BY operation results or the sorting of indexes on text columns.
- This parameter does not take effect for M-compatible databases.
- The value ranges of the lc_collate and lc_ctype parameters are related to the character sets supported by the local environment. For example, in the Linux OS, you can run the locale -a command to obtain the list of character sets supported by the OS. When the lc_collate and lc_ctype parameters are used, select the required character set and character type from the obtained character set list.
- If you want to set the character encoding set to GB18030_2022, ensure that the value ranges of lc_collate and lc_ctype are the same as those of GB18030.
- If you want to set the character encoding set to ZHS16GBK, ensure that the value ranges of lc_collate and lc_ctype are the same as those of GBK character set.
- LC_CTYPE [ = ] 'lc_ctype'
(Optional) Specifies the character type used by the new database.
Value range: character type supported by the OS. By default, the character type of the template database is used.
- The use of this parameter affects the classification of characters, such as uppercase letters, lowercase letters, and digits.
- This parameter does not take effect for M-compatible databases.
- The value ranges of the lc_collate and lc_ctype parameters are related to the character sets supported by the local environment. For example, in the Linux OS, you can run the locale -a command to obtain the list of character sets supported by the OS. When the lc_collate and lc_ctype parameters are used, select the required character set and character type from the obtained character set list.
- If you want to set the character encoding set to GB18030_2022, ensure that the value ranges of lc_collate and lc_ctype are the same as those of GB18030.
- If you want to set the character encoding set to ZHS16GBK, ensure that the value ranges of lc_collate and lc_ctype are the same as those of GBK character set.
- DBCOMPATIBILITY [ = ] 'compatibility_type'
(Optional) Specifies the compatible database type. The default value is MySQL.
Value range: MYSQL, TD, ORA, PG, and M. They represent MySQL-, TD-, Oracle-, PG-, and M-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 MySQL-compatible database converts the input to 0. In other compatible databases, an error is reported.
- In PG-compatible databases, CHAR and VARCHAR are counted by character. In other compatible databases, CHAR and VARCHAR are counted by byte. For example, for the UTF-8 character set, CHAR(3) can store three Chinese characters in PG-compatible databases, but can store only one Chinese character in other compatible databases.
- If an error occurs during statement execution, some error messages may display "A-format" or "B-format", which represent "ORA-format" and "MYSQL-format", respectively.
- For details about M-compatible database features, see M Compatibility Developer Guide.
- TABLESPACE [ = ] tablespace_name
(Optional) Specifies the tablespace of a database.
Value range: an existing tablespace name
- CONNECTION LIMIT [ = ] connlimit
(Optional) Specifies the maximum number of concurrent connections that can be made to the database.
Value range: an integer in the range [–1, 231 – 1]. The default value is –1, indicating that there is no limit.
- System administrators are not restricted by this parameter.
- connlimit of each node is calculated separately. 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.
- DBTIMEZONE [ = ] 'time_zone'
Specifies the time zone of the new database.
This parameter affects the time zone of the new database. The PRC is used by default.
The options are as follows:
- Time zone names and abbreviations supported by the OS.
- Timestamp ranges from –15:59 to +15:00.
Prerequisites: The currently connected database is an ORA-compatible database, a_format_version is set to '10c', and a_format_dev_version is set to 's2'.
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
-- Create users jim and tom. gaussdb=# CREATE USER jim PASSWORD '********'; -- Create the GBK-encoded database testdb1. gaussdb=# CREATE DATABASE testdb1 ENCODING 'GBK' template = template0; -- View information about the testdb1 database. gaussdb=# SELECT datname,pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'testdb1'; datname | pg_encoding_to_char ---------+--------------------- testdb1 | GBK (1 row) -- Create the ORA-compatible database testdb2 and specify jim as the owner. gaussdb=# CREATE DATABASE testdb2 OWNER jim DBCOMPATIBILITY = 'ORA'; -- View the information about testdb2. gaussdb=# SELECT t1.datname,t2.usename,t1.datcompatibility FROM pg_database t1,gs_user t2 WHERE t1.datname = 'testdb2' AND t1.datdba=t2.usesysid; datname | usename | datcompatibility ---------+---------+------------------ testdb2 | jim | ORA (1 row) gaussdb=# CREATE DATABASE testdb_tmp OWNER jim template=templatea; -- View the information about testdb2. gaussdb=# SELECT t1.datname,t2.usename,t1.datcompatibility FROM pg_database t1,gs_user t2 WHERE t1.datname = 'testdb_tmp' AND t1.datdba=t2.usesysid; datname | usename | datcompatibility ------------+---------+------------------ testdb_tmp | jim | ORA (1 row) -- Create a database that 1000 concurrent connections can be made. gaussdb=# CREATE DATABASE testdb4 CONNECTION LIMIT=1000; -- View the information about testdb4. gaussdb=# SELECT datname,datconnlimit FROM pg_database where datname = 'testdb4'; datname | datconnlimit ---------+-------------- testdb4 | 1000 (1 row) -- Create a tablespace. gaussdb=# CREATE TABLESPACE tbs_test RELATIVE LOCATION 'test_tablespace/tbs_test'; -- Create a database and specify tbs_test as the default tablespace. gaussdb=# CREATE DATABASE testdb5 TABLESPACE = tbs_test ; -- View the information about testdb5. gaussdb=# SELECT db.datname,ts.spcname FROM pg_database db JOIN pg_tablespace ts ON db.dattablespace = ts.oid WHERE db.datname = 'testdb5'; datname | spcname ---------+---------- testdb5 | tbs_test (1 row) -- Switch to the ORA-compatible database testdb2 and set session parameters. gaussdb=# \c testdb2 testdb2=# SET a_format_version='10c'; testdb2=# SET a_format_dev_version='s2'; -- Create the ORA-compatible database and specify the time zone. testdb2=# CREATE DATABASE testdb3 DBCOMPATIBILITY 'ORA' DBTIMEZONE='+08:00'; -- View the information about testdb3. testdb2=# SELECT datname,datcompatibility,dattimezone FROM pg_database WHERE datname = 'testdb3'; datname | datcompatibility | dattimezone ---------+------------------+------------- testdb3 | ORA | +08:00 (1 row) -- Switch to the initial database. Change the database name based on actual situation. testdb2=# \c postgres -- Drop databases. gaussdb=# DROP DATABASE testdb1; gaussdb=# DROP DATABASE testdb2; gaussdb=# DROP DATABASE testdb3; gaussdb=# DROP DATABASE testdb4; gaussdb=# DROP DATABASE testdb5; gaussdb=# DROP DATABASE testdb_tmp; -- Drop the user. gaussdb=# DROP USER jim; -- Drop the tablespace. gaussdb=# DROP TABLESPACE tbs_test;
Helpful Links
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', 'GB18030', 'GB18030_2022', or 'ZHS16GBK'), template [=] template0/templatea must be specified.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot