Working with Collations
1. Understanding Collations
| Concept | Detail |
|---|---|
| Collation | Sort/compare rules for text |
| Provider | libc (OS) or ICU (preferred) |
| Locale | en_US.UTF-8, en-US-x-icu, ... |
| Deterministic | Equal bytes ⇔ equal strings (default true) |
2. Listing Available Collations
SELECT collname, collprovider, collcollate, collctype
FROM pg_collation ORDER BY collname;3. Setting Database Collation
CREATE DATABASE shop
ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8'
ICU_LOCALE 'en-US' LOCALE_PROVIDER icu TEMPLATE template0;4. Using COLLATE Clause in Query
SELECT name FROM customers
ORDER BY name COLLATE "de-DE-x-icu";5. Using COLLATE in Index
CREATE INDEX customers_name_de ON customers (name COLLATE "de-DE-x-icu");6. Creating Custom Collation
CREATE COLLATION case_insens (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
SELECT 'AbC' = 'abc' COLLATE case_insens; -- true7. Using ICU Collations
CREATE COLLATION numeric_aware (provider = icu, locale = 'en-u-kn-true');
SELECT 'item10' < 'item2' COLLATE numeric_aware; -- false (natural sort)8. Using Libc Collations
CREATE COLLATION german (provider = libc, locale = 'de_DE.UTF-8');Warning: libc collations change with glibc upgrades — silently corrupts B-tree indexes. Use ICU.
9. Using Case-Insensitive Collations
CREATE COLLATION ci (provider=icu, locale='und-u-ks-level2', deterministic=false);
CREATE TABLE users (email text COLLATE ci UNIQUE);10. Understanding Collation Impact on Indexes
Note: Index uses column collation. Querying with a different COLLATE in WHERE bypasses the index unless an additional index on that collation exists.
11. Understanding Collation Impact on ORDER BY
SELECT name FROM customers ORDER BY name; -- column collation
SELECT name FROM customers ORDER BY name COLLATE "C"; -- byte order12. Altering Column Collation
ALTER TABLE customers ALTER COLUMN name TYPE text COLLATE "en-US-x-icu";
REINDEX TABLE customers; -- mandatory after collation change