Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ SQL Syntax/ SQL Statements/ C/ CREATE DATABASE
Updated on 2025-10-23 GMT+08:00
View PDF
Share

CREATE DATABASE

Description

DATABASE is the same as SCHEMA. This statement creates a schema.

Precautions

  • A user who has the CREATEDB permission or a system administrator can create a database.
  • 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 | SCHEMA} [IF NOT EXISTS] database_name [AUTHORIZATION role_id]
    [[create_option] [,...]]
create_option: [DEFAULT] {
    CHARACTER SET [=] default_charset
  | CHAR SET [=] default_charset
  | CHARSET [=] default_charset
  | COLLATE [=] default_collation
}

Parameters

  • database_name

    Specifies the database name.

    Value range: a string complying with Identifier Description.

  • AUTHORIZATION role_id

    (Optional) Specifies the owner of the created database. If omitted, the default owner is the current user.

    Value range: an existing username

  • COLLATE [=] collation_name

    (Optional) Specifies the character set used by the new database. For example, set this parameter by using 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). By default, the character set of the template database is used.

    Value range: See Database-level Character Sets and Collations.

  • {CHAR SET | CHARSET | CHARACTER SET} [=] charset_name

    (Optional) Specifies the character class used by the new database. For example, set this parameter by using CHARSET = 'zh_CN.gbk'. The use of this parameter affects the classification of characters, such as uppercase letters, lowercase letters, and digits. By default, the character classification of the template database is used.

    Value range: See Database-level Character Sets and Collations.

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 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 and switch to the test database.
m_db=# CREATE DATABASE test1;
m_db=# USE test1;
-- Create the a table.
m_db=# CREATE TABLE a(id int);
m_db=# DROP TABLE a;
-- Delete the database test1.
m_db=# USE public;
m_db=# DROP DATABASE test1;

Helpful Links

ALTER DATABASE and DROP DATABASE

Suggestions

  • 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', 'GB18030_2022', 'ZHS16GBK', or 'GB18030'), template [=] template must be specified.

Parent topic: C