skip to content

In PostgreSQL 18, how would you make dictionary headword lookups case-insensitive (a lower() index, citext, a nondeterministic ICU collation or casefold()) and what does each cost?

level: seniorimportance: should knowfreq 45%

answer

  1. who decides equality
  2. ks-level2 ignores case only
  3. citext folds with the database ctype
  4. ILIKE still refuses

basics

~20 s

A nondeterministic ICU collation such as und-u-ks-level2 makes equality, UNIQUE and, from 18, LIKE case-insensitive per column; citext lowercases under the database ctype; lower() or 18's casefold() expression indexes keep plain text but need matching queries.

solid answer

~40 s

There are four tools. A `lower(headword)` expression index works everywhere, but every query must repeat `lower()` on both sides. The `citext` extension makes that transparent, `UNIQUE` included, but it folds with `lower()` under the database's `LC_CTYPE`, handles case only and is slower than `text`. A nondeterministic ICU collation, `CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false)`, makes `=`, `UNIQUE`, `GROUP BY` and, from 18, `LIKE` case-insensitive, and can ignore accents at `ks-level1`; it costs speed and B-tree deduplication, `ILIKE` and regular expressions still raise errors, and 18 requires primary and foreign keys to agree on it. PostgreSQL 18's `casefold()` adds Unicode-correct folding for an expression index. For a new dictionary schema I would put the nondeterministic ICU collation on headword and treat citext as legacy.

code

sql · 14 lines
sql
CREATE COLLATION headword_ci (
  provider = icu, locale = 'und-u-ks-level2', deterministic = false);

CREATE TABLE headwords (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  lang     text NOT NULL,
  headword text COLLATE headword_ci NOT NULL,
  UNIQUE (lang, headword)
);

INSERT INTO headwords (lang, headword) VALUES ('de', 'Haus');
SELECT id FROM headwords WHERE headword = 'HAUS';    -- finds Haus
SELECT id FROM headwords WHERE headword LIKE 'HA%';  -- allowed from 18
INSERT INTO headwords (lang, headword) VALUES ('de', 'haus');  -- unique violation

go deeper

for a junior

Recall that a lower() expression index, the citext type and case-insensitive collations are the classic ways to match text regardless of case.

for a middle

Explain how a nondeterministic ICU collation differs from citext: who decides equality, what ks-level2 and ks-level1 ignore, and why citext follows the database LC_CTYPE.

for a senior

Justify a choice for a multilingual schema with its costs: lost B-tree deduplication, ILIKE and regex errors, 18's key-collation rule, and citext's locale limits.

for a principal

Set a team standard for caseless identifiers and search fields, including how existing citext columns migrate and how the choice survives major-version upgrades.

## The requirement A multilingual dictionary must find `Haus` when a user types `HAUS`, must refuse a second `haus` in the same language, and may also want `cafe` to match `café`. Plain `text` under a deterministic collation treats all of these as different: a deterministic collation breaks ties bytewise, so only identical byte sequences are equal. PostgreSQL offers four ways around that. ## Four mechanisms compared | mechanism | queries look like | case-insensitive UNIQUE | can ignore accents | main costs | |---|---|---|---|---| | `lower()` expression index | `WHERE lower(headword) = lower($1)` | unique index on `lower(headword)` | no | every query must match the expression | | `citext` type (contrib) | `WHERE headword = $1` | yes, plain `UNIQUE` | no | folds with the database `LC_CTYPE`; slower than `text`; no B-tree deduplication | | nondeterministic ICU collation | `WHERE headword = $1` | yes, plain `UNIQUE` | yes, at `ks-level1` | slower comparisons; no B-tree deduplication; some operators error | | `casefold()` expression index (18) | `WHERE casefold(headword) = casefold($1)` | unique index on `casefold(headword)` | no | needs a `UTF8` database; queries must match | ## Nondeterministic ICU collations A collation created with `deterministic = false` lets the provider decide equality without the bytewise tie-break. Only the ICU provider supports it. The ICU **strength** chooses what is ignored: - `und-u-ks-level2`: case ignored, accents significant (`Haus` = `HAUS`, `cafe` differs from `café`). - `und-u-ks-level1`: base letters only, so case and accents are both ignored. What you give up: 1. Comparisons are slower, and B-tree **deduplication** cannot be used on indexes with such a collation. 2. `ILIKE`, the regular-expression operators and `starts_with()` raise `nondeterministic collations are not supported for ...` errors. `LIKE` works from PostgreSQL 18; on 17 it errors as well. 3. Since 18, a primary key and the foreign keys referencing it must use deterministic collations or the same nondeterministic one. A dump restore, and therefore `pg_upgrade`, fails on a schema that breaks this. 4. A database's default collation cannot be nondeterministic; it is always declared per column or per expression. ## citext: what it really does `citext` is a trusted contrib extension, so `CREATE EXTENSION citext` works for a user with `CREATE` on the database. Its operators lowercase both sides with `lower()` and then compare normally, so `UNIQUE` and ordinary B-tree indexes become case-insensitive without changing queries. The limits matter for a multilingual app: - folding always follows the database `LC_CTYPE`, even when the column carries another `COLLATE`; - it is `lower()` equality, not Unicode case-insensitivity; - it handles case, never accents; - it is less efficient than `text`, and only `text` gets B-tree deduplication. The `citext` documentation itself suggests considering nondeterministic collations instead. ## casefold() in PostgreSQL 18 `casefold(text)` performs **case folding**, which exists for caseless matching rather than display. Under `PG_UNICODE_FAST` or ICU it handles characters with several lowercase forms and folds that change length (`ß` becomes `ss` under `PG_UNICODE_FAST`); under libc, which has no case folding, it is identical to `lower()`. It requires a `UTF8` server encoding and is immutable, so it can back an expression index. It suits a schema that keeps `text` semantics and opts into caseless matching query by query. ## Choosing 1. New column where equality and uniqueness must ignore case, or accents: a nondeterministic ICU collation. 2. Mostly case-sensitive data with occasional caseless lookups: `text` plus a `casefold()` (18) or `lower()` expression index. 3. An existing `citext` schema that works: keep it while knowing its locale limits, but do not reach for it by reflex in new designs. Switching an existing column to a case-insensitive collation has one trap: its unique constraint is rebuilt under the new equality and fails if case variants already exist. Find them first, for example with `SELECT lang, lower(headword) FROM headwords GROUP BY 1, 2 HAVING count(*) > 1`, and merge or rename those rows before running `ALTER TABLE headwords ALTER COLUMN headword TYPE text COLLATE headword_ci`. Fuzzy and accent-stripping search with trigram indexes and `unaccent` belongs to full text search; this answer is about equality, uniqueness and ordering.

  • What does PostgreSQL 18 require of foreign keys that reference a column with a nondeterministic collation?
    The referenced key and every referencing foreign key must use deterministic collations or the very same nondeterministic collation. A dump restore, and therefore `pg_upgrade`, fails on a schema that breaks this, so audit the collations of key columns before moving to 18.
  • If a citext column is declared with COLLATE "tr-x-icu", whose rules lowercase its values?
    The database's `LC_CTYPE`. citext's operators honour a non-default `COLLATE` only when comparing the already-lowercased strings; the folding itself always happens as though `COLLATE "default"` were given. Language-specific folding, such as Turkish dotted and dotless i, therefore follows the database locale, not the column's collation.
  • How would you also make cafe match café?
    Create the ICU collation at strength level 1, for example `locale = 'und-u-ks-level1'` with `deterministic = false`. At that level only base letters decide equality, so case and accents are both ignored. Neither citext nor `casefold()` can do this; `unaccent` is the text-search alternative.

  • citext compares strings with Unicode case folding, independent of the database locale.
  • A nondeterministic collation can be made the database default with CREATE DATABASE.
  • ILIKE works on nondeterministic collations in 18, just like LIKE.
  • casefold() is exactly the same as lower() under every provider.
  • Nondeterministic collations are free: B-tree deduplication still applies.

Assumes PostgreSQL 18: casefold() and LIKE on nondeterministic collations are new in 18, as is the rule that a key and its foreign keys use deterministic or identical nondeterministic collations.