CREATE DATABASE

Description

Creates a database. By default, the new database will be created only by cloning the standard system database template0.

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.

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 the naming convention.

• OWNER [ = ] user_name

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

  Value range: an existing username

• TEMPLATE [ = ] template

  (Optional) 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'

  (Optional) 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 OS 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	-
  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	-

  

  • 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 or ZHS16GBK, 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 need to use this function, you can add double quotation marks ("") to avoid this problem when creating and accessing objects.
  • If the client code is A and the server code is B, the conversion between encoding formats A and B must exist in the database. For details about encoding format conversion supported by the database, see the system catalog PG_CONVERSION. (If the encoding format cannot be converted, it is recommended that the encoding format on the client be the same as that on the server. You can change the encoding format on the client by setting the client_encoding parameter.)
  • If you want to set the database character set encoding to GB18030_2022 and the client encoding to GB18030_2022, ensure that the client OS supports GB18030_2022. If the GB18030 character set versions are incompatible with each other, data inconsistency may occur. In addition, if historical data needs to be switched to the GB18030_2022 database, follow the database switching process to migrate data.
• LC_COLLATE [ = ] 'lc_collate'

  (Optional) 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'

  (Optional) 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 OS, you can run the locale -a command to obtain the list of character sets supported by the OS. When using the lc_collate and lc_ctype parameters, you can select the required character sets and character classes.
  • 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 the GBK character set.
• DBCOMPATIBILITY [ = ] 'compatibility_type'

  (Optional) Specifies the compatible database type. The default value is MySQL.

  Value range: MYSQL, TD, ORA, and PG, indicating the MySQL-, Teradata- (TD-), Oracle-, and PostgreSQL-compatible databases, 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.
  • If an error is reported during statement execution, A-format or B-format is displayed in some error information. A-format indicates ORA-format, and B-format indicates MYSQL-format.
• TABLESPACE [ = ] tablespace_name

  (Optional) Specifies the tablespace of the database.

  Value range: an existing tablespace name

• CONNECTION LIMIT [ = ] connlimit

  (Optional) 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 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 in the range [–1,2³¹ – 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.
• DBTIMEZONE [ = ] 'time_zone'

  Specifies the time zone of the new database. For example, you can set this parameter by setting DBTIMEZONE to '+00:00'. This parameter affects the time zone of the new database. The PRC is used by default.

  Prerequisites: The current database is compatible with database ORA, a_format_version is set to '10c', and a_format_dev_version is set to 's2'.

  Value range: name and abbreviation of the time zone supported by the OS, or the timestamp ranges from -15:59 to +15:00.

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,pg_user t2 
          WHERE t1.datname = 'testdb2' AND t1.datdba=t2.usesysid;
 datname | usename | datcompatibility 
---------+---------+------------------
 testdb2 | jim     | ORA
(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.
testdb2=# \c postgres
-- Delete the database.
gaussdb=# DROP DATABASE testdb1;
gaussdb=# DROP DATABASE testdb2;
gaussdb=# DROP DATABASE testdb3;
-- Delete the user.
gaussdb=# DROP USER jim;

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', 'GB18030', 'GB18030_2022', or 'ZHS16GBK'), template [=] template0 must be specified.