CREATE DATABASE
Function
Creates a database. By default, the new database will be created only by cloning the standard system database template0.
Precautions
- Only system administrators or users with the CREATEDB permission can create a database.
- CREATE DATABASE cannot be executed within a transaction block.
- During the database creation, an error message indicating that permission denied is displayed, possibly because the permission on the data directory in the file system is insufficient. If an error message, indicating no space left on device is displayed, the possible cause is that the disk space is used up.
Syntax
CREATE DATABASE [IF NOT EXISTS] 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
Database name
Value range: String, which must comply with the naming convention.
OWNER [ = ] user_name
Specifies the owner of the new database. By default, the owner of a new database is the current user.
Value range: an existing user name.
TEMPLATE [ = ] template
Specifies a template name, that is, the template from which the database is created. openGauss creates a database by copying data from a template database. openGauss has two default template databases template0 and template1 and a default user database postgres.
Value range: template0
ENCODING [ = ] encoding
Specifies the encoding format used by the new database. The value can be a string (for example, SQL_ASCII) or an integer.
By default, the encoding format of the template database is used. The encoding formats of the template databases template0 and template1 depend on the OS. The encoding format of template1 cannot be changed. If you need to change the encoding format when creating a database, use template0.
Common values are GBK, UTF8, Latin1, and GB10830. The supported character sets are as follows:
Table 1 openGauss character set
Caution:
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 openGauss will not be able to help you convert or validate non-ASCII characters.
NOTICE:- 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 used directly 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 used directly 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 these characters, you can enclose them with double quotation marks ("") when creating and accessing objects to avoid this problem.
- If the client encoding is A and the server encoding is B, the conversion between encoding formats A and B must exist in the database. For example: If the encoding format on the server is gb18030, the error message “Conversion between GB18030 and GBK is not supported.” is displayed when you set the encoding format on the client to gbk because the current database does not support conversion between gb18030 and gbk. For details about all encoding formats supported by the database, see the pg_conversion system catalog.
LC_COLLATE [ = ] lc_collate
Specifies the character set used by the new database. For example, this parameter is set by running 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). The default is to use the collation order of the template database.
Value range: A valid order type.
LC_CTYPE [ = ] lc_ctype
Specifies the character class used by the new database. For example, this parameter is set by running lc_ctype = 'zh_CN.gbk'. The use of this parameter affects the classification of characters, such as uppercase letters, lowercase letters, and digits. The default is to use the character classification of the template database.
Value range: a valid character type
DBCOMPATIBILITY [ = ] compatibility_type
Specifies the compatible database type. The default value is O.
Value range: A, B, C, and PG, indicating O, MY, TD and POSTGRES databases, respectively.
NOTE:- For A 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 B 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 name of the tablespace that will be associated with the new 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.
NOTICE:- This limit does not apply to sysadmin.
- connlimit is calculated separately for each primary database node. Number of connections of the openGauss = connlimit x Number of normal CN master database nodes.
Value range: An integer greater than or equal to **-1**. The default value **-1** means 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.
- If the character encoding mode is SQL_ASCII and the modifier is an administrator, the character encoding mode can be different from the locale setting.
- The encoding and region settings must match the template database, except that template0 is used as a 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 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.
openGauss=# CREATE USER jim PASSWORD 'xxxxxxxxx';
openGauss=# CREATE USER tom PASSWORD 'xxxxxxxxx';
--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 jim as its owner.
openGauss=# CREATE DATABASE music2 OWNER jim;
--Create database music3 using template template0 and specify 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 tom.
openGauss=# ALTER DATABASE music2 OWNER TO tom;
--Set the tablespace of database music3 to PG_DEFAULT.
openGauss=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;
--Close the default index scan on database music3.
openGauss=# ALTER DATABASE music3 SET enable_indexscan TO off;
--Reset parameter enable_indexscan.
openGauss=# ALTER DATABASE music3 RESET enable_indexscan;
--Delete a database.
openGauss=# DROP DATABASE music2;
openGauss=# DROP DATABASE music3;
openGauss=# DROP DATABASE music4;
--Delete users jim and tom.
openGauss=# DROP USER jim;
openGauss=# DROP USER tom;
--Create a database compatible with Teradata.
openGauss=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'C';
--Create a database compatible with the A format.
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';
--Delete the databases that are compatible with the TD and A formats.
openGauss=# DROP DATABASE td_compatible_db;
openGauss=# DROP DATABASE ora_compatible_db;
Helpful Links
Optimization Suggestion
create database
Database cannot be created in a transaction.
ENCODING LC_COLLATE LC_CTYPE
If the new database Encoding does not match the template database (SQL_ASCII) ('GBK', 'UTF8', 'LATIN1', or 'GB18030'), template [=] template0 must be specified.