Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ Character Sets and Collations/ Schema-level Character Sets and Collations
Updated on 2025-08-19 GMT+08:00
View PDF

Schema-level Character Sets and Collations

Create a schema and specify the default character set and collation. For details about how to create a schema, see CREATE SCHEMA.

CREATE SCHEMA schema_name
 [ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] 
 [ [DEFAULT] COLLATE [ = ] default_collation ];

Modify the default character set and collation attributes of a schema. For details about how to modify a schema, see ALTER SCHEMA.

ALTER SCHEMA schema_name 
    [ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] [ [DEFAULT] COLLATE [ = ] default_collation ];

Parameter description:

  • schema_name

    Specifies the schema name.

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

  • default_charset

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

  • default_collation

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

GaussDB selects a character set and collation of a 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 specified, the schema has no default character set or default collation.

  • 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 MySQL-compatible mode (sql_compatibility = 'MYSQL') support default_collation. If another collation is specified, an error is reported.

  • The character set and collation of the new schema must be server_encoding of the database.

Example:

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

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

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

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