List Collations in Your PostgreSQL Database

calendar Apr 19, 2026 · 4 min read · postgresql administration database pg_collation  ·
Share on: linkedin copy

How to List Collations Available in PostgreSQL

A collation defines the rules for sorting and comparing text. It controls how ORDER BY handles strings, whether a and A compare as equal, and how accented characters rank relative to unaccented ones. Every text column in PostgreSQL has a collation, either inherited from the database default or set explicitly on the column or expression.

Knowing which collations are available is useful when creating columns that need locale-specific sorting, diagnosing unexpected sort order in query results, or migrating data between databases with different locale settings.

Sample Code

List all collations using pg_collation:

 1SELECT
 2    collname                           AS collation_name,
 3    pg_catalog.pg_get_userbyid(collowner) AS owner,
 4    collprovider                       AS provider,
 5    collencoding                       AS encoding,
 6    collcollate                        AS lc_collate,
 7    collctype                          AS lc_ctype
 8FROM
 9    pg_catalog.pg_collation
10ORDER BY
11    collname;

List collations using the SQL-standard view:

 1SELECT
 2    collation_name,
 3    character_set_catalog,
 4    character_set_schema,
 5    character_set_name,
 6    pad_attribute
 7FROM
 8    information_schema.collations
 9ORDER BY
10    collation_name;

Find collations matching a specific locale:

 1SELECT
 2    collname,
 3    collcollate,
 4    collctype
 5FROM
 6    pg_catalog.pg_collation
 7WHERE
 8    collcollate ILIKE '%en_US%'
 9ORDER BY
10    collname;

Notes: Works on all supported PostgreSQL versions. pg_collation was introduced in PostgreSQL 9.1. The collprovider column (libc, icu, default) was added in PostgreSQL 10.

Code Breakdown

  • pg_catalog.pg_collation — The system catalog that stores one row for every collation available in the database cluster. Collations come from the operating system (libc) or from ICU if PostgreSQL was compiled with ICU support.
  • collname — The collation name as referenced in SQL, for example "en-US-x-icu" or "C" or "POSIX".
  • collprovider — The library that implements the collation: d = database default, c = libc, i = ICU.
  • collencoding — The encoding the collation applies to. -1 means it applies to all encodings.
  • collcollate — The LC_COLLATE locale string (controls sort order).
  • collctype — The LC_CTYPE locale string (controls character classification: upper/lower, digit, etc.).
  • pg_catalog.pg_get_userbyid(collowner) — Converts the owner OID to a role name.
  • information_schema.collations — The SQL-standard view. Simpler but shows less detail than pg_collation.

Key Points

  • The C and POSIX collations sort by raw byte value. They are the fastest option but sort uppercase before lowercase and do not respect locale-specific character ordering.
  • The default collation is whatever the database was created with — typically inherited from the OS locale at initdb time.
  • ICU collations (prefix und-x-icu, en-US-x-icu, etc.) offer more consistent cross-platform behavior than libc collations.
  • A collation mismatch between two columns or expressions will cause a could not determine which collation to use error.

Insights and Explanations

How collations affect query results:

Collation determines how ORDER BY, DISTINCT, GROUP BY, index comparisons, and string operators (=, <, >) behave on text. Two databases with different collations can return rows in different orders for the same query, which surprises developers moving data between environments.

Example — sort order differs between collations:

1-- Sort using C collation (byte order): uppercase before lowercase
2SELECT name FROM mytable ORDER BY name COLLATE "C";
3
4-- Sort using locale-aware collation
5SELECT name FROM mytable ORDER BY name COLLATE "en-US-x-icu";

Check the collation of the current database:

1SELECT
2    datname,
3    datcollate,
4    datctype
5FROM
6    pg_database
7WHERE
8    datname = current_database();

Check the collation of a specific column:

1SELECT
2    column_name,
3    collation_name
4FROM
5    information_schema.columns
6WHERE
7    table_name = 'mytable'
8    AND table_schema = 'public';

Setting collation on a column:

1CREATE TABLE products (
2    id   serial PRIMARY KEY,
3    name text COLLATE "en-US-x-icu"
4);

Setting collation for a single query:

1SELECT name
2FROM products
3ORDER BY name COLLATE "C";

Additional Considerations

  • ICU availability: ICU collations are only available if PostgreSQL was compiled with --with-icu. Check with SELECT pg_catalog.pg_get_collationbyid(100) or look for icu in the collprovider column.
  • Index collation: A text index uses the collation of the column by default. If you query with a different collation, the index may not be used.
  • Deterministic vs. non-deterministic: PostgreSQL 12+ supports non-deterministic collations (ICU only) where 'A' = 'a' can be true. Use CREATE COLLATION with deterministic = false to create one.
  • Performance: The C collation is fastest for indexes and sorting. Use locale-aware collations only when required for correct alphabetic ordering.

References

Posts in this series