Re: ICU database?

Zwettler Markus (OIZ) wrote:

> I'm creating a PG cluster with Swiss High German ICU locales.
>
> I wonder why "collate = C" and "ctype = C" is shown?

initdb produces locale-related output that might be interesting
to look at, as well as the result of `locale` in the shell.

Anyway, trying to reproduce what you get, it does not appear
to be obvious.

This invocation:

$ LANG=de_CH.utf-8 /usr/lib/postgresql/18/bin/initdb \
--locale-provider=icu \
--icu-locale=de-CH-x-icu \
-D /tmp/pgdata

results in:

[...]
Verwende Sprach-Tag »de-CH-x-icu« für ICU-Locale »de-CH-x-icu«.
Der Datenbankcluster wird mit dieser Locale-Konfiguration initialisiert
werden:
Locale-Provider: icu
Standardsortierfolge: de-CH-x-icu
LC_COLLATE: de_CH.utf-8
LC_CTYPE: de_CH.utf-8
LC_MESSAGES: de_CH.utf-8
LC_MONETARY: fr_FR.UTF-8
LC_NUMERIC: fr_FR.UTF-8
LC_TIME: fr_FR.UTF-8
Die Standarddatenbankkodierung wurde entsprechend auf »UTF8« gesetzt.
Die Standardtextsuchekonfiguration wird auf »german« gesetzt.

This is what I would expect (the fr_FR.UTF-8 values coming from my
environment)

To get "C" as the locale for template databases despite LANG, I need
to force it through LC_* env variables (an unusual setup):

LC_ALL=C LANG=de_CH.utf-8 /usr/lib/postgresql/18/bin/initdb \
--locale-provider=icu \
--icu-locale=de-CH-x-icu \
-D /tmp/pgdata

results in:

[...]
Using language tag "de-CH-x-icu" for ICU locale "de-CH-x-icu".
The database cluster will be initialized with this locale configuration:
locale provider: icu
default collation: de-CH-x-icu
LC_COLLATE: C
LC_CTYPE: C
LC_MESSAGES: C
LC_MONETARY: C
LC_NUMERIC: C
LC_TIME: C
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
[...]

Can you check if LC_something in your environment is overriding
LANG?

As an aside, the "x-icu" suffix is not needed in ICU locales.
It's not harmful but might be confusing.
"x-icu" has been put into Postgres ICU collation names generated
by initdb to distinguish them from libc collations.
Collation names can be any invented identifier, whereas locale names
are passed to the locale provider and must mean something to them.

In other words, considering this in an existing database:

select * from pg_collation where collname='de-CH-x-icu' \gx
-[ RECORD 1 ]-------+------------
oid | 12461
collname | de-CH-x-icu
collnamespace | 11
collowner | 10
collprovider | i
collisdeterministic | t
collencoding | -1
collcollate |
collctype |
colliculocale | de-CH
collicurules |
collversion | 153.120

The kind of value that initdb expects for the --icu-locale argument
is the value found in "colliculocale", not the value found in
"collname". The value in "collname" could be 'xyz', it would not
make any difference to ICU, since what it gets is the value
from "colliculocale". However passing "xyz" to --icu-locale
will not produce anything good.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/