List PostgreSQL Collations with pg_collation and ICU

calendar Apr 19, 2026 / · 6 min read · postgresql administration collation pg_collation pg_database  ·
Share on: linkedin copy

List Collations Available in PostgreSQL

pg_collation is the system catalog that holds every collation a database can use — the sort-and-compare rules behind ORDER BY, DISTINCT, comparison operators and text indexes. One query against it shows each collation's name, the library that implements it, and the locale it maps to, which is the first thing to check when text sorts differently on two servers.

Purpose and Overview

A collation decides how text is ordered and compared. In PostgreSQL the collation feature "allows specifying the sort order and character classification behavior of data per-column, or even per-operation," so one database can hold columns that sort by different rules.

Collations come from three providers. libc collations come from the operating system's locales, icu collations come from the ICU library when PostgreSQL is built with it, and from PostgreSQL 17 there is also a builtin provider. Because libc and icu collations take their rules from libraries outside PostgreSQL, the sort order depends on which library and version the server has.

The queries below list every collation with its provider and locale, narrow the list to one locale, and show which collation the current database uses by default. That covers the usual questions: what is available, which one should a new column use, and why two environments disagree.

Sample Code

List all collations with their provider and locale:

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 pg_catalog.pg_collation
9ORDER BY collname;

Find the collations for one locale (PostgreSQL 17 and later):

1SELECT collname, collprovider, collcollate, collctype, colllocale
2FROM pg_catalog.pg_collation
3WHERE collcollate ILIKE '%en_US%'
4   OR colllocale  ILIKE '%en_US%'
5ORDER BY collname;

Check the current database's default collation:

1SELECT datname, datcollate, datctype
2FROM pg_database
3WHERE datname = current_database();

Notes: The first and third queries run on PostgreSQL 10 and later. The locale query needs PostgreSQL 17 or later; on 15 and 16 replace colllocale with colliculocale, and on 14 and earlier drop that column and the OR line. Adjust the '%en_US%' pattern to the locale you need.

Code Breakdown

The main query reads one row per collation from pg_collation and turns the owner OID into a role name.

The pg_collation columns

The pg_collation catalog defines each column. collprovider is the "provider of the collation: d = database default, b = builtin, c = libc, i = icu." collencoding is the "encoding in which the collation is applicable, or -1 if it works for any encoding." collcollate and collctype hold the LC_COLLATE and LC_CTYPE values, but only for libc collations: "if the provider is not libc, collcollate is NULL and colllocale is used instead."

The owner lookup

pg_get_userbyid(collowner) converts the owner's OID into a role name.

Why the locale query checks two columns

Because ICU and builtin collations leave collcollate empty, a search on collcollate alone finds only libc collations. The OR colllocale condition catches the rest. In PostgreSQL 17, colllocale is the "collation provider locale name for this collation object"; in 15 and 16 the same role was played by colliculocale, which covered ICU only.

The database default

pg_database.datcollate and datctype show the collation and character classification the database was created with. Every text column that does not name a collation uses this default.

Key Collation Providers

C and POSIX

C and POSIX are equivalent. They "sort by byte values rather than natural language order, and only the ASCII letters A through Z are treated as letters." The documentation describes their behavior as "efficient and stable across all versions for a given database encoding," which makes C a safe choice for identifiers and codes where alphabetical order for people does not matter.

libc

libc collations use the operating system's locales, such as en_US.utf8. They use "the locales provided by the operating system C library" — the same locales most other tools on that server use — so their order follows that library.

ICU

ICU collations are named "in BCP 47 language tag format, with a private use extension -x-icu appended, to distinguish them from libc locales" — for example de-x-icu for German, or und-x-icu, the ICU root locale ("undefined"). ICU behavior can change when PostgreSQL is built against a different ICU version.

Deterministic and nondeterministic

A deterministic collation "considers strings to be equal only if they consist of the same byte sequence." A nondeterministic one may treat different byte sequences as equal — for example for case-insensitive comparison. The documentation warns of a performance cost and notes that B-tree indexes cannot use deduplication with a nondeterministic collation. Create one with CREATE COLLATION and deterministic = false.

Practical Applications

Diagnosing a sort-order difference

When the same ORDER BY returns rows in a different order on two servers, compare datcollate on both databases and the column's collation. If they match, compare the provider and collversion, the provider-specific version of the collation: libc and ICU rules come from the server's libraries.

Choosing a collation for a new column

Run the locale query to see what is installed, then set the column's collation explicitly, for example name text COLLATE "de-x-icu". A COLLATE clause can also be added to a single query, such as ORDER BY name COLLATE "C", without changing the column.

Explaining a collation error

When an expression combines inputs with different collations, PostgreSQL applies its derivation rules: "If any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised." Listing the collations involved shows which COLLATE clause to add or remove.

Comparing with the SQL-standard view

information_schema.collations lists the same collations with only four columns — collation_catalog, collation_schema, collation_name and pad_attribute. The last is "always NO PAD," since PostgreSQL does not support PAD SPACE. For provider and locale details, use pg_collation.

Version Compatibility

pg_collation first appears in the PostgreSQL 9.1 documentation. collprovider was added in PostgreSQL 10, together with ICU support, and collisdeterministic in PostgreSQL 12, when nondeterministic collations arrived.

The locale column has changed twice. PostgreSQL 15 and 16 have colliculocale, which holds the ICU locale. PostgreSQL 17 replaced it with colllocale, which covers both ICU and the new builtin provider. Queries written for one version will fail on the other, so check the server version before reusing a script across clusters.

ICU itself became a default part of source builds: in current releases you build without it using --without-icu, while PostgreSQL 15 and earlier needed --with-icu to include it. Check for i in collprovider to confirm a server has it.

Best Practices

  • Set collations explicitly on columns that need a specific sort order — do not rely on whatever the server's default locale happens to be.
  • Use C for codes and identifiers — byte order is stable across versions for a given encoding.
  • Record each collation's provider and collversion before an OS or ICU upgrade — the rules come from libraries outside PostgreSQL.
  • Prefer ICU for consistent natural-language sorting across platforms — and keep the ICU version in mind when you upgrade.
  • Test nondeterministic collations before production — they cost performance and block some index and pattern-matching features.

References

Posts in this series