Encoding und Collation

Encoding pro Datenbank

Postgres legt das Encoding pro Datenbank fest. Das gesamte Cluster läuft mit einem einheitlichen template0/template1-Default, jede neue Datenbank erbt das — kann es aber überschreiben.

SHOW server_encoding;
--  server_encoding
-- -----------------
--  UTF8

SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype
FROM pg_database;

Empfehlung: UTF8 für alles. Latin1, SQL_ASCII oder andere Legacy-Encodings sind nur dann sinnvoll, wenn man Daten aus uralten Systemen importiert und Konvertierung nicht möglich ist. Selbst dann ist die übliche Strategie: importieren, konvertieren, in UTF8-DB ablegen.

SQL_ASCII ist eine besondere Falle: Postgres prüft dabei gar nichts, sondern speichert Bytes wie sie kommen. Mischformate, ungültige Sequenzen, schief codierte Strings — alles möglich. Niemals SQL_ASCII für neue Datenbanken.

Encoding beim CREATE DATABASE setzen

CREATE DATABASE myapp
    WITH OWNER = app_owner
         ENCODING = 'UTF8'
         LC_COLLATE = 'C.UTF-8'
         LC_CTYPE = 'C.UTF-8'
         TEMPLATE = template0;

Wichtig: das TEMPLATE = template0 ist nicht optional, wenn du Encoding/Collation abweichend vom Cluster-Default setzen willst. template1 (der Default) erbt seine Settings vom Cluster und blockiert, wenn du andere wählst. template0 ist eine pristine Kopie ohne Modifikationen — von dort darf man frei wählen.

Collation — Sortier- und Vergleichsregeln

Collation definiert, wie Strings sortiert und verglichen werden. ORDER BY name mit deutscher Collation sortiert „Ä” anders als mit englischer.

PostgreSQL kennt zwei Provider für Collations:

Auf einer modernen Postgres-Installation (PG 16+) kommt UTF8+ICU als Default. Auf älteren Installationen oder bei Upgrade von altem Datenbestand bleibt häufig libc — und das ist die Quelle eines klassischen Problems:

Der „collation version mismatch“-Bug

Wenn du Postgres mit libc-Collations betreibst und das Betriebssystem auf eine neue glibc-Version upgrade’st, kann sich die Sortierordnung von Strings ändern. Postgres bemerkt das und gibt eine Warnung:

WARNING:  collation "de_DE.UTF-8" has version mismatch
DETAIL:  The collation in the database was created using version 2.31,
         but the operating system provides version 2.35.
HINT:  Rebuild all objects affected by this collation and run
         ALTER COLLATION pg_catalog."de_DE.UTF-8" REFRESH VERSION,
         or build PostgreSQL with the right library version.

Konkret heißt das: Indexe, die auf string-vergleichenden Collations basieren (im Wesentlichen alle B-tree-Indexe auf text/varchar), könnten falsche Daten enthalten — ihre interne Reihenfolge passt nicht mehr zur tatsächlichen libc-Sortierung. Ein WHERE name = 'foo' kann Treffer übersehen.

Dieses Risiko betrifft besonders:

• Container-Migrationen (postgres:14-bullseye → postgres:14-bookworm)
• OS-Upgrades auf der Datenbank-Maschine (Ubuntu 22.04 → 24.04)
• Cross-Plattform-Restores (Linux → macOS oder umgekehrt)

Lösung 1: Indexe mit string-Spalten neu aufbauen.

REINDEX DATABASE myapp;

Lösung 2 (besser, langfristig): auf ICU-Collations umstellen — die sind versioniert und ändern sich bei OS-Upgrades nicht.

ICU-Collations nutzen

Datenbank gleich mit ICU anlegen:

CREATE DATABASE myapp
    WITH OWNER = app_owner
         ENCODING = 'UTF8'
         LOCALE_PROVIDER = 'icu'
         ICU_LOCALE = 'de-DE'
         TEMPLATE = template0;

Eigene ICU-Collations definieren und auf Spaltenebene nutzen:

CREATE COLLATION german_phonebook (
    provider = icu,
    locale = 'de-u-co-phonebk'
);

CREATE TABLE contacts (
    id   serial primary key,
    name text COLLATE german_phonebook
);

german_phonebook sortiert „Ä” wie „Ae” — typische deutsche Telefonbuch-Sortierung. ICU-Locale-Strings folgen BCP 47 und sind sehr ausdrucksstark.

Die „magische” C.UTF-8-Collation

Eine besondere Variante: C.UTF-8 (oder schlicht C) sortiert byte-weise, nicht sprachsensitiv. Vorteile:

• Schneller als Sprach-Collations (kein Unicode-Lookup nötig)
• Stabil (keine Versions-Probleme)
• Funktioniert mit LIKE '...%'-Patterns auf Index-Ebene direkt (sprachsensitive Collations brauchen oft text_pattern_ops-Index)

Nachteile:

• Sortierung ist „A-Z, dann a-z” und „Ä” hinter „Z” — nicht das, was Menschen erwarten

Faustregel: für rein technische Daten (UUIDs, E-Mail-Adressen, URLs, Slug-Felder) ist C.UTF-8 die beste Wahl. Für menschenlesbare Namen, Titel, Adressen lohnen sich sprachsensitive ICU-Collations.

Du kannst beides mischen — die DB-Default-Collation ist eine Sache, einzelne Spalten können ihre eigene Collation haben:

CREATE TABLE users (
    id     serial primary key,
    email  text COLLATE "C.UTF-8",       -- E-Mail: byte-weise sortieren
    name   text COLLATE "de-DE-x-icu"    -- Name: deutsch sortieren
);

Weiterführende Ressourcen

Externe Quellen

• Localization – PostgreSQL Documentation
• Character Set Support
• Collation Support
• CREATE DATABASE – PostgreSQL Documentation
• PostgreSQL Wiki: Locale data changes
• BCP 47 / Unicode Locale Identifiers

Verwandte Artikel

• Konfigurationsdateien
• Datenbank umbenennen und Owner wechseln
• Benutzer und Datenbank erstellen
• Tablespaces