In PostgreSQL, how does a COLLATE clause decide which collation a comparison or ORDER BY uses, and when can an index no longer serve it?
answer
- explicit beats implicit beats default
- two implicit collations collide
- 42P22 at run time
- one collation per index column
basics
~20 sAn explicit COLLATE beats a column's implicit collation, which beats the default; two different implicit collations in one comparison fail with SQLSTATE 42P22. Each index column has one collation, so a query asking for another cannot use that index.
solid answer
~50 sEvery `text`, `varchar` or `char` expression carries a collation: a column's declared one, the default for a literal, or an explicit one from `COLLATE`. When an operator combines inputs, an explicit `COLLATE` wins, and two different explicit ones are an error; otherwise a non-default implicit collation beats the default. If two columns bring different non-default implicit collations, the result is indeterminate: harmless for `||`, but `<` then fails at run time with `could not determine which collation to use for string comparison` (SQLSTATE 42P22), and `ORDER BY` on such an expression is an error too. An index takes its column's collation, one per key column, so an index on a `de-x-icu` headword cannot return rows in `sv-x-icu` order; for that you add `CREATE INDEX ... (headword COLLATE "sv-x-icu")` and write queries with exactly that `COLLATE`.
code
sql · 17 linesCREATE TABLE entries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
headword text COLLATE "de-x-icu" NOT NULL,
gloss text COLLATE "sv-x-icu"
);
CREATE INDEX entries_headword_idx ON entries (headword);
-- German order: can come from entries_headword_idx
SELECT headword FROM entries ORDER BY headword LIMIT 20;
-- Swedish order: needs a sort, or this second index
CREATE INDEX entries_headword_sv_idx ON entries (headword COLLATE "sv-x-icu");
SELECT headword FROM entries ORDER BY headword COLLATE "sv-x-icu" LIMIT 20;
-- Conflicting implicit collations: fails with 42P22 once a row is compared
SELECT id FROM entries WHERE headword < gloss;
SELECT id FROM entries WHERE headword < gloss COLLATE "de-x-icu";go deeper
Recall that COLLATE picks the sort rules for one column or expression, and that names like "de-x-icu" must be double-quoted.
Explain explicit versus implicit derivation, why a literal never overrides a column, and why comparing two differently collated columns fails with 42P22.
Show how collation choices shape indexing: one collation per index column, an extra index per extra sort order, and queries written to match it exactly.
Decide where collations are declared across a schema, column defaults versus query-level COLLATE, so sort orders stay predictable and the index count stays bounded.
## Every text expression carries a collation Collations apply to **collatable** types: `text`, `varchar`, `char`, domains over them, and extension types such as `citext`. Each expression of such a type has a collation: - a **column reference** has the column's declared collation (`headword text COLLATE "de-x-icu"`), or the database default if none was declared; - a **constant** has the default collation of its type; - a **COLLATE clause** attaches an explicit collation to any expression; - a **function or operator** result takes the collation derived from its inputs. The collation matters wherever PostgreSQL orders or classifies text: `ORDER BY`, the comparison operators, B-tree indexes, `upper()`, `lower()` and `initcap()`, pattern matching, and `to_char`. ## How collations combine A collation's **derivation** is explicit when it comes from `COLLATE` and implicit otherwise. When an operator or function has several collatable inputs: 1. If any input is explicit, all explicit collations must be the same, or the query fails at parse time; that explicit collation is the result. 2. Otherwise, a non-default implicit collation wins over the default collation. 3. If two inputs carry *different* non-default implicit collations, the combination is **indeterminate**. That is not an error by itself; it becomes one only when the operation needs a collation. With a column `a` declared `COLLATE "de-x-icu"` and `b` declared `COLLATE "sv-x-icu"`: | expression | outcome | |---|---| | `a < 'foo'` | compared with `de-x-icu`: the column beats the literal's default | | `a < ('foo' COLLATE "fr-x-icu")` | compared with `fr-x-icu`: explicit wins | | `a < b` | run-time error, SQLSTATE 42P22 `indeterminate_collation` | | `a < b COLLATE "de-x-icu"` | works: one explicit collation settles it | | `a \|\| b` | works: concatenation does not need a collation | | `ORDER BY a \|\| b` | error: the sort needs one; add `COLLATE` to resolve it | | `a COLLATE "de-x-icu" < b COLLATE "sv-x-icu"` | parse-time error, SQLSTATE 42P21 `collation_mismatch` | A bare string literal, one without its own `COLLATE`, never overrides a column: it carries the default collation, which always loses to a non-default implicit one. ## Collations and indexes A B-tree index stores keys in the order of **one collation per key column**, by default the column's own. The planner uses it for ordering or range conditions only when the query compares with that same collation: - `ORDER BY headword` and `WHERE headword > 'K'` on the `de-x-icu` column can use an index on `(headword)`. - `ORDER BY headword DESC` can too, because a B-tree is read backwards in the same collation. - `ORDER BY headword COLLATE "sv-x-icu"` cannot: the stored order is German. Either sort at query time, or add a second index declared `(headword COLLATE "sv-x-icu")` and make queries name exactly that collation. The best-known special case, an index declared with `COLLATE "C"` or a pattern operator class so that `LIKE 'abc%'` can use a B-tree, belongs to B-tree pattern indexing. ## The dictionary app: one column, several sort orders A multilingual dictionary shows German readers German order and Swedish readers Swedish order, which places `ä` and `ö` after `z` rather than beside `a` and `o`. Practical rules: 1. Declare each column with the collation most queries need, so the plain index serves them. 2. Add one index per extra collation that must be served from an index, and weigh its write and storage cost against sorting at query time with a `LIMIT`. 3. Quote collation names: they are identifiers, `"de-x-icu"` contains hyphens and `"C"` is uppercase, while unquoted names are folded to lowercase. 4. List what exists with psql's `\dOS+` or `SELECT collname, collprovider FROM pg_collation;`; ICU names exist only on builds with ICU support. 5. Check what a table really uses with psql's `\d entries`, whose Collation column is filled in for every column whose collation differs from its type's default. Function results follow the same rules: `lower(headword)` keeps the `de-x-icu` collation of its input, so an `ORDER BY lower(headword)` sorts in German order, and an expression index on `lower(headword)` is built in that order too. Getting the derivation right also keeps errors out of production: a view that compares two columns with different collations may run fine until the first row reaches the comparison, then fail with 42P22. One explicit `COLLATE` in the view definition settles it for good.
- Why must collation names such as de-x-icu or C be written in double quotes?Collation names are SQL identifiers. Unquoted identifiers are folded to lowercase and cannot contain hyphens, so `COLLATE de-x-icu` is a syntax error and `COLLATE C` looks for a collation named `c`. Writing `COLLATE "de-x-icu"` or `COLLATE "C"` uses the exact catalog name.
- Where else besides the query can a collation be set, and which level wins?From broadest to narrowest: `initdb` for the template databases, `CREATE DATABASE` for one database, the column definition, and finally a `COLLATE` clause on an expression. Each narrower level overrides the broader ones, so a query-level `COLLATE` always has the last word for that expression.
- COLLATE only changes ORDER BY output; WHERE comparisons ignore it.
- An index on headword serves ORDER BY headword under any collation.
- Comparing two differently collated columns silently uses the database default.
- Concatenating two differently collated columns with || raises a collation error.
- A string literal in WHERE overrides the column's collation.