pg_collation — PostgreSQL system catalog

The PostgreSQL pg_collation system catalog: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  2 min read

Summary

The catalog pg_collation describes the available collations, which are essentially mappings from an SQL name to operating system locale categories. See collation for more information.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_collation system catalog exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • oid oid
    Row identifier
  • collname name
    Collation name (unique per namespace and encoding)
  • collnamespace oid references pg_namespace.oid
    The OID of the namespace that contains this collation
  • collowner oid references pg_authid.oid
    Owner of the collation
  • collprovider char
    Provider of the collation: d = database default, b = builtin, c = libc, i = icu
  • collisdeterministic bool
    Is the collation deterministic?
  • collencoding int4
    Encoding in which the collation is applicable, or -1 if it works for any encoding
  • collcollate text
    LC_COLLATE for this collation object. If the provider is not libc, collcollate is NULL and colllocale is used instead.
  • collctype text
    LC_CTYPE for this collation object. If the provider is not libc, collctype is NULL and colllocale is used instead.
  • colllocale text
    Collation provider locale name for this collation object. If the provider is libc, colllocale is NULL; collcollate and collctype are used instead.
  • collicurules text
    ICU collation rules for this collation object
  • collversion text
    Provider-specific version of the collation. This is recorded when the collation is created and then checked when it is used, to detect changes in the collation definition that could lead to data corruption.

Related catalogs

This object references the following other system catalogs:

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_collation.