LC_COLLATE and LC_CTYPE Options

Querying Character Sets Supported by LC_COLLATE and LC_CTYPE

Run the following SQL statement to query character sets (also called encodings) supported by LC_COLLATE and LC_CTYPE:

SELECT pg_encoding_to_char(collencoding) AS encoding,collname,collcollate AS "LC_COLLATE",collctype AS "LC_CTYPE" FROM pg_collation;

If encoding is empty, LC_COLLATE supports all character sets.

Configuring the Collation of a Database in a Locale

Collation information includes LC_COLLATE and LC_CTYPE. For details, see the official documentation.

• LC_COLLATE

  The default value is en_US.utf8.

  Comparison of the same string in different collations may have different results.

  For example, after you execute SELECT 'a'>'A';, the result is false if this parameter is set to en_US.utf8 and the result is true if this parameter is set to C. If you need to migrate a database from Oracle to RDS for PostgreSQL, set LC_COLLATE to C. You can query the supported collations from the pg_collation table.

• LC_CTYPE

  It is used to classify if a character is a digit, uppercase letter, lowercase letter, and so on. You can query the supported character classifications from the pg_collation table.

How to Use

Run the following command to create a database with LC_COLLATE and LC_CTYPE set to zh_CN.utf8:

CREATE DATABASE my_db WITH ENCODING = 'UTF8' LC_COLLATE ='zh_CN.utf8' LC_CTYPE ='zh_CN.utf8' TEMPLATE = template0 ;

If the specified LC_COLLATE is incompatible with the character set, error information similar to the following is displayed:

1. The specified LC_COLLATE and LC_CTYPE must be compatible with the target character set. Otherwise, an error is reported.
2. The LC_COLLATE and LC_CTYPE settings of an existing database cannot be changed by running the ALTER DATABASE statement. You can change them while creating a new database and then import your data to the new database.