Updated on 2025-05-29 GMT+08:00

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

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/templatea must be specified.