Collations, encodings and locales

conceptThe concept

A database has a default collation (datcollate, datlocprovider), and any column, index or expression can override it with COLLATE. The C collation sorts by byte value: fast, stable, and odd for humans (all upper case before lower case). Linguistic collations such as en-US sort the way people expect, at a CPU cost.

A nondeterministic collation can treat different strings as equal: 'Asha' and 'ASHA' under a case-insensitive ICU collation. Unique constraints, =, GROUP BY and DISTINCT all follow it. Since 18, LIKE works with them too.

The encoding (server_encoding) is almost always UTF8. client_encoding is converted automatically. SQL_ASCII means "no checking at all" and lets invalid bytes into the database.

whyWhy it matters

Collation problems are invisible until they are expensive: a query that does not use an index because of a COLLATE mismatch, a unique constraint that lets Admin and admin both register, or a replica on a newer OS whose indexes no longer find rows. The fix for a corrupted index is a REINDEX; the fix for the wrong database collation is a dump and reload, so choosing well at creation time matters.

Use caseReal-world scenario

A team upgrades the primary's OS from a glibc 2.28 distribution to 2.36 and leaves the data directory in place. Queries on the customers(name) index start missing rows that a sequential scan finds, and a unique index accepts a duplicate. The server log shows a collation version mismatch warning. They rebuild every index on text columns with REINDEX ... CONCURRENTLY, then run ALTER DATABASE ... REFRESH COLLATION VERSION. For the next cluster they choose the builtin C.UTF-8 locale for the database and ICU collations only on the columns that need linguistic order.

Try itExamples

Run and edit these in the interactive lesson.

SELECT collprovider, count(*) AS collations
FROM pg_collation
GROUP BY collprovider
ORDER BY collprovider;
SELECT datname, datlocprovider, datcollate, datcollversion
FROM pg_database
WHERE datname = current_database();
SHOW server_encoding;

SELECT string_agg(x, ' ' ORDER BY x COLLATE "C") AS c_order,
       string_agg(x, ' ' ORDER BY x COLLATE "und-x-icu") AS icu_order
FROM unnest(ARRAY['banana', 'Apple', 'apple', 'Banana', 'éclair', 'eclair']) AS x;

BEGIN;
CREATE COLLATION case_insensitive (provider = icu, locale = 'und@colStrength=secondary', deterministic = false);
CREATE TABLE app_users (email text COLLATE case_insensitive UNIQUE);
INSERT INTO app_users VALUES ('Asha@Example.com');
INSERT INTO app_users VALUES ('ASHA@EXAMPLE.COM') ON CONFLICT DO NOTHING;
SELECT email, email = 'asha@example.com' AS matches,
       email LIKE 'asha@%' AS like_matches
FROM app_users;
ROLLBACK;

SELECT upper('straße' COLLATE pg_c_utf8) AS c_utf8,
       upper('straße' COLLATE pg_unicode_fast) AS unicode_fast,
       lower('ÉCOLE' COLLATE "C") AS c_lower,
       lower('ÉCOLE' COLLATE pg_c_utf8) AS c_utf8_lower,
       length('école') AS chars, octet_length('école') AS bytes;

SELECT collname, collprovider, collversion,
       pg_collation_actual_version(oid) AS library_version,
       collversion IS DISTINCT FROM pg_collation_actual_version(oid) AS mismatch
FROM pg_collation
WHERE collname IN ('pg_c_utf8', 'unicode', 'und-x-icu')
ORDER BY collname;

-- the warning you will see at connect time
-- WARNING:  database "shop" has a collation version mismatch
-- DETAIL:  The database was created using collation version 2.28, but the operating system provides version 2.36.

-- indexes with at least one key column using a libc or ICU collation
SELECT DISTINCT i.indexrelid::regclass AS index_name
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
JOIN pg_collation c ON c.oid = a.attcollation
WHERE c.collprovider IN ('c', 'i', 'd') AND c.collname NOT IN ('C', 'POSIX');

REINDEX INDEX CONCURRENTLY customers_name_idx;      -- each affected index
ALTER DATABASE shop REFRESH COLLATION VERSION;       -- then record the new version
ALTER COLLATION "en-US-x-icu" REFRESH VERSION;

-- choosing at creation time (17+)
createdb --locale-provider=builtin --builtin-locale=C.UTF-8 -E UTF8 -T template0 shop
createdb --locale-provider=icu --icu-locale=en-US -E UTF8 -T template0 shop_reports

internalsUnder the hood

B-tree indexes store keys in collation order. If the collation's rules change, the stored order no longer matches the comparison function and searches walk past the right leaf page. PostgreSQL records the collation version (pg_collation.collversion, pg_database.datcollversion) when the object is created and compares it with the library version at connection time (pg_collation_actual_version(), pg_database_collation_actual_version()), warning on a mismatch. It does not rebuild anything itself. Collations whose order is code-point based (C, the builtin provider) have no external version to drift.

Avoid / preferDo this, not that

-- primary on glibc 2.28, new standby built on glibc 2.36
-- streaming replication, fail over to it when needed

-- keep primary and physical standbys on the same OS release and libc/ICU versions,
-- or use the builtin provider so the order does not depend on the OS

SELECT * FROM app_users WHERE email ILIKE 'asha@example.com';

-- either a nondeterministic collation on the column
-- or an expression index
CREATE UNIQUE INDEX ON app_users (lower(email));
SELECT * FROM app_users WHERE lower(email) = lower('Asha@Example.com');

createdb -E SQL_ASCII shop

createdb -E UTF8 --locale-provider=builtin --builtin-locale=C.UTF-8 -T template0 shop

PitfallsCommon mistakes

• Adding COLLATE to a query and expecting the existing index (built with a different collation) to be used.
• Upgrading the OS of a server in place without checking pg_collation_actual_version and rebuilding text indexes.
• Assuming the database collation can be changed with ALTER DATABASE; it is fixed at creation and needs a dump and reload.
• Making every column nondeterministic: comparisons are slower and some operations need deterministic collations.

LimitsWhen not to use it

Do not reach for a nondeterministic collation just to make one search case-insensitive; an expression index on lower(col) is simpler. Do not use linguistic collations on identifier-like columns (codes, hashes, UUID text) that only need equality and stable order; C or the builtin provider is faster and never drifts.

SecuritySecurity notes

Uniqueness that depends on collation is an account-takeover risk: if Admin and admin are different users, an attacker can register a look-alike. Enforce case-insensitive uniqueness for usernames and emails, and normalise Unicode (normalize(name, NFC), name IS NFC NORMALIZED) so visually identical strings with different code points cannot both exist.

ChallengeTry it yourself

BEGIN;
CREATE COLLATION natural_ci (provider = icu, locale = 'und@colStrength=secondary;colNumeric=yes', deterministic = false);
SELECT string_agg(x, ', ' ORDER BY x COLLATE natural_ci) AS sorted
FROM unnest(ARRAY['item 10', 'item 9', 'Item 100', 'item 1']) AS x;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Data types
• Type conversion