collation-providerlisted

PostgreSQL's collation-provider abstraction — the 3-way split between `builtin` (PG-owned), `icu` (ICU library), and `libc` (OS-provided). Covers `src/backend/utils/adt/pg_locale.c` (the dispatcher) + `pg_locale_builtin.c` / `pg_locale_icu.c` / `pg_locale_libc.c` (per-provider implementations) plus the encoding conversion layer (`src/backend/utils/mb/`). Loads when the user asks about `CREATE COLLATION`, provider semantics, `LC_COLLATE` / `LC_CTYPE`, ICU integration + version tracking, the `builtin` provider added PG 17, `collversion` upgrade detection, why an ORDER BY differs between PGs on the same OS, `pg_c_utf8` builtin, or multibyte encoding conversion. Skip when the ask is about client encoding (server-client convert is included but psql `\encoding` is client-side), full-text search dictionaries (different subsystem `tsearch`), or `CREATE TEXT SEARCH CONFIGURATION`.
matejformanek/postgres-claude · ★ 0 · Code & Development · score 70

Install: claude install-skill matejformanek/postgres-claude

# collation-provider — builtin, ICU, libc — the 3-way split Every text comparison in PG (equality, LIKE, ORDER BY, index key comparison) goes through a **collation**. Collations are catalog objects (`pg_collation`) that tell the backend WHICH library to use for comparison, WHICH locale within that library, and WHICH encoding the strings are in. Since PG 17, collations come from **three providers**: - `builtin` — PG-owned; deterministic Unicode + a few special cases. Added PG 17 to avoid OS/library upgrade breakage. - `icu` — ICU library (International Components for Unicode). Rich, version-tracked, portable across OSes. - `libc` — OS-provided (glibc / macOS / Windows). Fast + backwards-compatible + fragile (OS upgrades change collation order silently). ## The file map | File | KB | Role | |---|---:|---| | `utils/adt/pg_locale.c` | 50 | The dispatcher. `pg_locale_deterministic`, `pg_strcoll`, `pg_strxfrm`, provider dispatch table. Handles the fast paths (deterministic C-locale). | | `utils/adt/pg_locale_builtin.c` | 7 | PG-owned builtin provider — `C.UTF-8`, `unicode`, `PG_C_COLLATION_OID`. Small because the rules ARE the code. | | `utils/adt/pg_locale_icu.c` | 36 | ICU integration — UCollator setup, version reading, `u_strcmp` etc. Conditional on `--with-icu`. | | `utils/adt/pg_locale_libc.c` | 34 | libc integration — `newlocale` / `strcoll_l` / `strxfrm_l`. Historical + widely deployed. | | `include/utils/pg_locale.h` | 8 | Public API surface + `pg_locale_t` opaque hand