Help Center > > Developer Guide> SQL Reference> SQL Syntax> CREATE DATABASE

CREATE DATABASE

Updated at: Jul 15, 2020 GMT+08:00

Function

CREATE DATABASE creates a database. By default, the new database will be created by cloning the standard system database template1. A different template can be specified using TEMPLATE template name.

Precautions

  • A user that has the CREATEDB permission or a sysadmin can create a database.
  • CREATE DATABASE cannot be executed inside 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
CREATE DATABASE database_name
    [ [ WITH ] { [ OWNER [=] user_name ] |
               [ TEMPLATE [=] template ] |
               [ ENCODING [=] encoding ] |
               [ LC_COLLATE [=] lc_collate ] |
               [ LC_CTYPE [=] lc_ctype ] |
               [ DBCOMPATIBILITY [=] compatibilty_type ] |
               
               [ CONNECTION LIMIT [=] connlimit ]}[...] ];

Parameter Description

  • database_name

    Indicates the database name.

    Value range: a string. It must comply with the naming convention rule.

  • OWNER [ = ] user_name

    Indicates the owner of the new database. By default, the owner of the database is the current user.

    Value range: an existing user name

  • TEMPLATE [ = ] template

    Indicates the template name, that is, the name of the template to be used to create the database. DWS creates a database by coping a database template. DWS has two default template databases template0 and template1 and a default user database postgres.

    Value range: An existing database name. If this it is not specified, the system copies template1 by default. Its value cannot be postgres.

    Currently, database templates cannot contain sequences. If a database template contains sequences, database creation using this template will fail.

  • 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 vary based on OS environments by default. The encoding format of template1 cannot be changed. If you need to change the encoding format of a database later, use template0 to create the database.

    Common values: GBK, UTF8, and Latin1

    The character set encoding of the new database must be compatible with the local settings (LC_COLLATE and LC_CTYPE).

  • LC_COLLATE [ = ] lc_collate

    Specifies the collation order to use in the new database. For example, this parameter can be set using lc_collate = 'zh_CN.gbk'.

    The use of this parameter affects the sort order applied to strings, for example, in queries with ORDER BY, as well as the order used in 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 classification to use in the new database. For example, this parameter can be set using lc_ctype = 'zh_CN.gbk'. The use of this parameter affects the categorization of characters, for example, lower, upper and digit. The default is to use the character classification of the template database.

    Value range: A valid character type.

  • DBCOMPATIBILITY [ = ] compatibilty_type

    Specifies the compatible database type.

    Valid values: TD and ORA, indicating the compatibility with Teradata and Oracle, respectively. If this parameter is not specified, the default value ORA is used.

  • 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

    Indicates the maximum number of concurrent connections that can be made to the new database.

    Value range: An integer greater than or equal to -1. The default value -1 means no limit.

    • This limit does not apply to sysadmin.
    • To ensure the proper running of a cluster, the minimum value of CONNECTION LIMIT is the number of CNs in the cluster, because when a cluster runs ANALYZE on a CN, other CNs will connect with the running CN for metadata synchronization. For example, if there are three CNs in the cluster, set CONNECTION LIMIT to 3 or a greater value.

The following are limitations on character encoding:

  • If the locale is C (or equivalently POSIX), then all encoding modes are allowed, but for other locale settings only the encoding 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.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Create users jim and tom:
CREATE USER jim PASSWORD 'Bigdata123@';
CREATE USER tom PASSWORD 'Bigdata123@';

-- Create database music using GBK (the local encoding type is also GBK):
CREATE DATABASE music ENCODING 'GBK' template = template0;

-- Create database music2 and specify JIM as its owner:
CREATE DATABASE music2 OWNER jim;

-- Create database music3 using template template0 and specify jim as its owner:
CREATE DATABASE music3 OWNER jim TEMPLATE template0;

-- Set the maximum number of connections to database music to 10:
ALTER DATABASE music CONNECTION LIMIT= 10;

-- Rename database music to music4:
ALTER DATABASE music RENAME TO music4;

-- Change the owner of database music2 to tom:
ALTER DATABASE music2 OWNER TO tom;

-- Set the tablespace of database music3 to PG_DEFAULT:
ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;

-- Close the default index scan on database music3:
ALTER DATABASE music3 SET enable_indexscan TO off;

-- Reset parameter enable_indexscan:
ALTER DATABASE music3 RESET enable_indexscan;

Delete the databases:
DROP DATABASE music2;
DROP DATABASE music3;
DROP DATABASE music4;

-- Delete users jim and tom:
DROP USER jim;
DROP USER tom;

-- Create a database compatible with Teradata:
CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'TD';

-- Create a database compatible with Oracle:
CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';

-- Delete the databases compatible with Teradata or Oracle:
DROP DATABASE td_compatible_db;
DROP DATABASE ora_compatible_db;

Helpful Links

ALTER DATABASE, DROP DATABASE

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel