• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE DATABASE

CREATE DATABASE

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

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

  • 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

    Specifies the name of the template from which to create a database. DWS creates a database by coping a database template.

    Value range: An existing database name (not including POSTGRES database).

  • ENCODING [ = ] encoding

    Indicates the character set encoding to use in the new database. The value of this parameter can be a string (for example 'SQL_ASCII') or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). If the encoding of the new database does not match the template data, set template to template0.

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

    Value range: GBK, UTF8, and Latin1

  • 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.

  • 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.

    This limit does not apply to sysadmin.

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

The following are limitations on character encoding:

  • If the locale is C (or equivalently POSIX), then all encodings 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 supported by the environment.

Example

-- 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 music1;
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 TABLE, DROP DATABASE