Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ Character Sets and Collations/ Database-level Character Sets and Collations
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Database-level Character Sets and Collations

Create a database or schema and specify the default character set and collation.

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...
create_option: [DEFAULT] {
    CHARACTER SET [=] default_charset
  | CHAR SET [=] default_charset
  | CHARSET [=] default_charset
  | COLLATE [=] default_collation
}

Modify the default character set and collation attributes of the database or schema.

ALTER {DATABASE | SCHEMA} db_name
    [create_option] ...
create_option: [DEFAULT] {
    CHARACTER SET [=] default_charset
  | CHAR SET [=] default_charset
  | CHARSET [=] default_charset
  | COLLATE [=] default_collation
}

Syntax description:

  • db_name

    Specifies the database or schema name.

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

  • default_charset

    Specifies the default character set of a database or schema. If you specify a character set separately, the default collation of the database or schema is set to the default collation of the specified character set.

  • default_collation

    Specifies the default collation of a database or schema. If you specify a collation separately, the default character set of the database or schema is set to the character set corresponding to the specified collation.

GaussDB selects a character set and collation of a database or schema in the following ways:

  • If both default_charset and default_collation are set, the character set default_charset and collation default_collation are used. In addition, default_charset and default_collation must correspond to each other. Otherwise, an error is reported.

  • If only default_charset is set, the character set default_charset and its default collation are used.

  • If only default_collation is set, the collation default_collation and its corresponding character set are used.

  • If neither default_charset nor default_collation is set, the character set of the database or scheme is the same as server_encoding, and the collation is the default collation of the character set.

  • Only character sets with a default collation support default_charset. If the specified character set does not have a default collation, an error is reported.
  • Only the collations in Supported Collations support default_collation. If another collation is specified, an error is reported.
  • Except BINARY character sets, the character set of a new library or schema must be server_encoding of the database.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
-- Set only the character set. The collation is the default collation of the character set.
m_db=# CREATE SCHEMA test CHARSET utf8;

-- Set only the collation. The character set is the character set associated with the collation.
m_db=# CREATE SCHEMA test COLLATE utf8_bin;

-- Set both the character set and collation. The character set and collation must correspond to each other.
m_db=# CREATE SCHEMA test CHARSET utf8 COLLATE utf8_bin;

-- Change the default character set of test to utf8mb4 and the default collation to utf8mb4_bin.
m_db=# ALTER SCHEMA test CHARSET utf8mb4 COLLATE utf8mb4_bin;

-- Drop the table.
m_db=# DROP SCHEMA test;