CREATE DATABASE

Function

CREATE DATABASE creates a database.

GaussDB(DWS) creates a database by copying a database template. GaussDB(DWS) has two default template databases template0 and template1 and a default user database gaussdb. By default, a new database is created based on template1. The character encoding of template1 cannot be changed. If you want to change it, you can set the template parameter to template0.

Precautions

• A user that has the CREATEDB permission or a sysadmin can create a database.
• CREATE DATABASE cannot be executed within a transaction block.
• Errors along the line of "could not initialize database directory" are most likely related to insufficient permissions on the data directory, a full disk, or other file system problems.

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 ] |
               [ DBCOMPATIBILITY_BEHAVIOR [=] opt_compat_behavior] |
               
               [ CONNECTION LIMIT [=] connlimit ]}[...] ];

Parameter Description

Character Set Encoding Description

• To view the character encoding of the current database, run the show server_encoding; command.
• To make your database compatible with most characters, you are advised to use the UTF8 encoding when creating a database.

• The character set encoding of the new database must be compatible with the language settings (LC_COLLATE and LC_CTYPE).
• If the language is C (or equivalently POSIX), then all encoding modes are allowed, but for other language settings only the encoding mode for character sets consistent with that of the locale will work properly.
• The encoding and locale settings must match those of the template database, except when template0 is used as template. This is because other databases might contain data that does not match the specified encoding, or might contain indexes whose sort ordering is affected by LC_COLLATE and LC_CTYPE. Copying such data would result in a database that is corrupt according to the new settings. template0, however, is known to not contain any data or indexes that would be affected.
• Supported encoding depends on the environment. If the message "invalid locale name" is displayed, run the locale -a command to check the encoding set supported by the environment.
• When the specified character encoding set is GBK, some uncommon Chinese characters cannot be directly used as object names. This is because when the encoding range of the second byte of GBK is between 0x40 and 0x7E, the byte encoding overlaps with the ASCII character @A-Z[\]^_`a-z{|}. @[\]^_?{|} 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 really want to use it, you can avoid this problem by adding double quotation marks when creating and accessing objects.
• In the current version, the GBK character set supports the character €, which is represented as 0x80 in hexadecimal code. You can use the € character in the GBK library, and the GBK character set of GaussDB(DWS) is compatible with the CP936 character set. Note that the GBK character set is approximately equal to the CP936 character set, but the GBK character set does not contain the definition of the character €.

Examples

Create database music using GBK (the local encoding type is also GBK).

1

CREATE DATABASE music ENCODING 'GBK' template = template0;

Create database music2 and specify jim as its owner.

1
2

CREATE USER jim PASSWORD "{Password}";
CREATE DATABASE music2 OWNER jim;

Create database music3 using template template0 and specify jim as its owner.

1

CREATE DATABASE music3 OWNER jim TEMPLATE template0;

Create a database compatible with Oracle and set the connection limit to 10.

1

CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA' CONNECTION LIMIT = 10;

Helpful Links

ALTER DATABASE, DROP DATABASE

FAQs

How Do I View and Set the Character Set Encoding Format of the GaussDB(DWS) Database?

Query Results Are Inconsistent in Oracle, Teradata, and MySQL Compatibility Modes