Unicode Collation
Collation defines how text sorts and compares — case, accents, locale rules — and mismatches between columns, literals, and indexes corrupt equality semantics and index usage.
Definition
A collation is the rule set for ordering and comparing strings: linguistic locale, case sensitivity, accent sensitivity, and numeric handling. PostgreSQL encodes collation per column or expression with libc or ICU providers; MySQL folds it into character set pairs. Mismatched collations between operands force conversion or errors, and indexes are collation-bound.
Context and scope
Hits multilingual products on email or username uniqueness, ORDER BY locale expectations, and JOINs between columns created with different defaults or versions.
Common misconceptions
- UTF-8 storage solves international text — storage encodes; collation decides equality and order, which is where bugs live
- Equality is byte equality — case- and accent-insensitive collations make distinct byte strings equal, deliberately
- Collations are stable forever — ICU version changes can change ordering and break index-based uniqueness checks across upgrades
Anti-patterns
- Creating columns across migrations with different collation defaults and joining them into conversion errors
- Building case-sensitive unique usernames in an email system where users type case variants
- Sorting user-visible lists by binary collation and shipping wrong locale order for non-English text
Related concepts
Related examples
Authoritative source
What it is
Text needs three decisions: encoding (what bytes mean), and collation (how strings compare and sort). The same Unicode string sorts differently in Swedish and German locales; a case-insensitive collation treats Ada and ada as one value. Equality, uniqueness, ORDER BY, and range scans all flow from collation.
How it works
PostgreSQL attaches collation at column or expression level, with libc or ICU providers; nondeterministic collations even treat canonically different byte sequences as equal. MySQL pairs character set with collation per column. Indexes embed the collation: changing it requires rebuilding them, and comparisons across collations may error or convert.
When to care
Identity fields needing case- or accent-insensitivity at the database level, multilingual ordering, and schema evolution across engine or ICU upgrades. Pick explicit collations at design time rather than inheriting defaults per migration.
Pitfalls
Collation drift between environments surfaces as query errors only when mixed operands meet. Upgrades that change ICU versions can reorder text and invalidate unique indexes built on the old rules. Test migrations against real multilingual data, including combining accents and full-width forms.