undefined_object collation “…” for encoding “…” does not exist — 42704

Symptoms

A statement referenced a collation that does not exist for the database’s encoding. PostgreSQL raises SQLSTATE 42704 (undefined_object).

• The named collation is unknown or invalid for this encoding.
• Common with locale names not installed on the OS.
• Collations are encoding-specific.

What the server log shows

ERROR:  collation "de_DE" for encoding "UTF8" does not exist

Why PostgreSQL raises this — what the manual says

As Section 24.2 Collation Support explains:

The statement referenced a collation that is not defined for the database’s encoding; each collation is tied to particular encodings, so choose a collation compatible with the current database/column encoding.

Collations map to OS/ICU locales and are valid only for matching encodings. A collation name that isn’t defined for the current encoding cannot be resolved, so PostgreSQL reports 42704.

Common causes

• The locale is not installed on the server OS.
• A collation name not defined for the database encoding.
• A typo in the collation/locale name.

How to fix it

1. List available collations: SELECT collname FROM pg_collation;.
2. Install the required locale on the OS, then run pg_import_system_collations or create the collation.
3. Use an ICU collation (e.g. "de-DE-x-icu") available in your build.

Related & next steps

• SQLSTATE 42704 — Undefined Object (full reference)
• Message: could not determine which collation to use
• Glossary: Collation

Reference: PostgreSQL 18 Section 24.2 “Collation Support”.