PostgreSQL Collations and Locale Cheat Sheet

SELECT n.nspname AS schema_name, c.collname,
       CASE c.collprovider
         WHEN 'b' THEN 'builtin'
         WHEN 'c' THEN 'libc'
         WHEN 'i' THEN 'icu'
         WHEN 'd' THEN 'database default'
       END AS provider,
       c.collisdeterministic, c.collencoding, c.collversion
FROM pg_collation AS c
JOIN pg_namespace AS n ON n.oid = c.collnamespace
ORDER BY n.nspname, c.collname;

BEGIN;
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION app_owner;
CREATE COLLATION app.en_sort (
  provider = icu,
  locale = 'en-US'
);
CREATE COLLATION app.unicode_fast (
  provider = builtin,
  locale = 'PG_UNICODE_FAST'
);
COMMIT;

CREATE COLLATION app.case_insensitive (
  provider = icu,
  locale = 'und-u-ks-level2',
  deterministic = false
);

SELECT 'Résumé' COLLATE app.case_insensitive = 'RÉSUMÉ' AS same_casefold,
       U&'\0061\0308' COLLATE app.case_insensitive = U&'\00E4' AS same_normalized;

CREATE TABLE app.people (
  person_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name text COLLATE app.en_sort NOT NULL
);

SELECT person_id, display_name
FROM app.people
ORDER BY display_name COLLATE pg_catalog."C", person_id;

CREATE INDEX people_name_en_idx
  ON app.people (display_name COLLATE app.en_sort);

CREATE UNIQUE INDEX people_name_ci_key
  ON app.people (display_name COLLATE app.case_insensitive);

EXPLAIN (COSTS OFF)
SELECT display_name
FROM app.people
ORDER BY display_name COLLATE app.en_sort;

SELECT n.nspname, c.collname, c.collversion AS recorded_version,
       pg_collation_actual_version(c.oid) AS actual_version
FROM pg_collation AS c
JOIN pg_namespace AS n ON n.oid = c.collnamespace
WHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid);

SELECT pg_describe_object(d.refclassid, d.refobjid, d.refobjsubid) AS collation,
       pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent_object
FROM pg_depend AS d
JOIN pg_collation AS c ON d.refclassid = 'pg_collation'::regclass AND d.refobjid = c.oid
WHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)
ORDER BY 1, 2;

-- Run outside a transaction block when using CONCURRENTLY.
REINDEX INDEX CONCURRENTLY app.people_name_en_idx;

-- Refresh only after every dependent object has been rebuilt and checked.
ALTER COLLATION app.en_sort REFRESH VERSION;

REVOKE CREATE ON SCHEMA app FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA app TO locale_admin;

SELECT current_setting('server_version') AS server_version,
       n.nspname, c.collname, c.collprovider, c.collisdeterministic,
       c.collversion, pg_collation_actual_version(c.oid) AS actual_version
FROM pg_collation AS c
JOIN pg_namespace AS n ON n.oid = c.collnamespace
WHERE n.nspname = 'app'
ORDER BY c.collname;