Schema-level Character Sets and Collations

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

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

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

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

Syntax description:

• schema_name

  Specifies the schema name.

  Value range: a string. It must comply with the identifier 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 B-compatible mode (sql_compatibility is set to 'B') support default_collation. If another collation is specified, an error is reported.

• The character set 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;