RDS for PostgreSQL Collation Settings

What Is Collation?

In RDS for PostgreSQL, a collation defines the sort order and character classification rules for a given character set, including case sensitivity, accent handling, and whitespace rules. It determines string comparison logic, sorting results, and the behavior of certain character-processing functions. PostgreSQL allows you to specify collations at the database, column, or query expression level. If no collation is explicitly specified, the system automatically inherits the database's default collation.

Core Locale Parameters (LC_COLLATE and LC_CTYPE)

When you initialize a database cluster (initdb) or create a new database (using CREATE DATABASE), the LC_COLLATE and LC_CTYPE parameters must be configured. The two parameters come from the OS's locale standards (ISO C and POSIX) and form the foundation of database collations.

• LC_COLLATE (string sort order)
  • Definition: controls the lexicographical order and collation rules of characters and determines their precedence during string comparisons.
  • Impact scope:
    • ORDER BY sorting results
    • String comparison operators (>, <, >=, <=, BETWEEN, and so on)
    • Value selection of aggregate functions (MIN() and MAX())
    • Physical build order and range scan paths of B-tree indexes
  • Examples: Given the dataset ['a', 'A', 'b', 'B'], different collations produce different ascending sorting results.

    Table 1 Sorting results under different collations

    Collation (LC_COLLATE)	Sorting Result (Ascending)	Description
    C or POSIX	'A', 'B', 'a', 'b'	Sorts by ASCII byte values. Uppercase letters take precedence.
    en_US.UTF-8	'a', 'A', 'b', 'B'	Follows English dictionary order, typically by logical grouping.
    fr_FR.UTF-8	'a', 'b', 'A', 'B'	Follows French sorting conventions, arranging specific characters according to language rules.

    

    Once a database is created, modifying LC_COLLATE is strictly prohibited. PostgreSQL B-tree indexes strictly rely on the collation defined at creation time. Forcibly changing LC_COLLATE causes the physical order of existing indexes to conflict with the new logical collation, which can lead to incorrect query results or data corruption.

• LC_CTYPE (character classification)
  • Definition: defines the semantic classification of characters and determines how the database identifies character types (such as letters, digits, punctuation, and whitespace).
  • Impact scope:
    • Case conversion functions: UPPER(), LOWER(), and INITCAP()
    • Character classification checks: underlying functions such as isalpha(), isdigit(), and isspace()
    • Regular expression matching: POSIX regular expression character classes (such as [[:alpha:]], [[:digit:]], \w, and \s)
  • Examples:
    • Regular expression matching: The result of SELECT 'a' ~ '^[[:alpha:]]$'; depends on how LC_CTYPE defines the boundaries of alphabetic characters.
    • Linguistic behavior: In the Turkish locale (tr_TR), the uppercase format of lowercase i is İ (dotted capital I) rather than I. This conversion is strictly controlled by LC_CTYPE.

Three Levels of Using Collations

• Database level

  A collation is specified when you create a database and used as the default collation for all new objects in the database.

  CREATE DATABASE mydb LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' TEMPLATE = template0; 

  Once a database is created, its collation is fixed. To change the collation, you must migrate the data to a new database.

• Column level (physical storage attribute)

  A collation can be specified for a specific column when you define table structures. It affects index creation and default sorting behavior of that column.

  CREATE TABLE test1 ( id integer, content varchar COLLATE "es_ES" ); 

  Indexes created on this column strictly follow the specified collation. If a query performs sorting without explicitly specifying COLLATE, the column's defined collation is applied automatically.

• Expression level (dynamic override)

  A collation can be dynamically specified in SELECT, ORDER BY, or filtering clauses to override default collation behavior temporarily.

  • Override the default collation for a query.

    SELECT * FROM test1 ORDER BY content COLLATE "en_US";

  • Enforce the C collation in comparison operations.

    SELECT * FROM test1 WHERE content = 'ä' COLLATE "C";

Deterministic and Non-deterministic Collations

• Deterministic collation: PostgreSQL 12 introduced the concept of deterministic collations, which are mainly used with the ICU provider.
• Non-deterministic collation: It treats strings with the same base characters but different forms as equal (for example, ignoring accents or treating ä and ae as equivalent).

  Creation example:

  CREATE COLLATION und_deterministic ( provider = icu, locale = 'und-u-ks-level1', deterministic = false ); 

  If deterministic is not explicitly specified, the system defaults to deterministic mode (deterministic = true).

Common Operations

• Viewing the current collation configuration

  Run the \l command in psql to view the locale settings of each database, or run the following SQL statement:

  SELECT datname, datcollate, datctype FROM pg_database WHERE datname NOT IN ('template0', 'template1'); 

• Specifying a collation when creating a database

  To create a database with collation settings different from the cluster default (that is, template1), you must specify template0 as the template database.

  CREATE DATABASE my_new_db TEMPLATE template0 LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' ENCODING 'UTF8';

• Creating a custom collation

  If built-in collations do not meet your workload needs, you can create custom collations.

  • Based on libc

    Use this method when the target locale has been installed on the underlying OS.

    CREATE COLLATION german_phonebook ( provider = libc, lc_collate = 'de_DE.utf8', lc_ctype = 'de_DE.utf8' );

  • Based on ICU

    ICU provides more fine-grained internationalization rules and does not require pre-configurations on the OS.

    CREATE COLLATION "de-u-co-phonebk-x-icu" ( provider = icu, locale = 'en-US' ); 

    Advantages: A single node supports thousands of detailed collation rules. The collation behavior is consistent across platforms and is not affected by OS upgrades.

• Viewing system-supported collations

  Query the system catalog to view all supported collations and their providers:

  SELECT collname, collprovider, collcollate, collctype FROM pg_collation;

  Field description: In collprovider, c indicates libc, i indicates ICU, and d indicates the default collation.

FAQ