Column-level Character Sets and Collations

You can set the character set and collation for each column of the string type (CHAR, VARCHAR, or TEXT).

CREATE TABLE table_name (
    column_name data_type
	[ CHARACTER SET | CHARSET charset ]
    [ COLLATE collation ]
);

Syntax description:

• table_name

  Specifies the name of the table.

• data_type

  Specifies the data type of a column. The value can be character set or collation syntax.

• CHARACTER SET | CHARSET charset

  Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

• COLLATE collation

  The COLLATE clause specifies the collation of a column (the data type of the column must support collation). If no collation is specified, the default collation is used.

GaussDB selects a character set and collation of a table column in the following ways:

• If both charset and collation are specified, charset and collation are used. charset and collation must correspond to each other. Otherwise, an error is reported.

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

• If only collation is specified, the character set associated with collation and the specified collation are used.

• If neither charset nor collation is specified, the default character set and collation of the table are used.

• 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 support default_collation. If another collation is specified, an error is reported.

• If a table column is of the text type and the specified collation is binary, the text type is converted to the corresponding binary type and the collation is the specified binary collation.

• The character set of the partition key of the partitioned table must be the same as that of the database.

• Currently, the default character set of a table column must be server_encoding of the database.

Example:

1
2
3
4
5
6
7
8

-- Set only the character set. The collation is the default collation of the character set.
gaussdb=# CREATE TABLE test(c1 text CHARSET utf8);

-- Set only the collation. The character set is the character set associated with the collation.
gaussdb=# CREATE TABLE test(c1 text COLLATE utf8_bin);

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