ICU database?
I'm creating a PG cluster with Swiss High German ICU locales.
I wonder why "collate = C" and "ctype = C" is shown?
initdb --locale-provider=icu --icu-locale=de-CH-x-icu -D /path/to/datainitdb
CREATE DATABASE eng001
WITH
OWNER = dbowner
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'de-CH-x-icu'
;
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+-------+-------------+-----------+-----------------------
eng001 | dbowner | UTF8 | icu | C | C | de-CH-x-icu | | =CTc/dbowner +
| | | | | | | | dbowner=CTc/dbowner
postgres | postgres | UTF8 | icu | C | C | de-CH-x-icu | |
template0 | postgres | UTF8 | icu | C | C | de-CH-x-icu | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | icu | C | C | de-CH-x-icu | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
On 8/5/26 5:55 AM, Zwettler Markus (OIZ) wrote:
I'm creating a PG cluster with Swiss High German ICU locales.
What Postgres version?
What OS and version?
What is the OS locale?
I wonder why “collate = C” and “ctype = C” is shown?
initdb --locale-provider=icu --icu-locale=de-CH-x-icu -D /path/to/
datainitdbCREATE DATABASE eng001
WITH
OWNER = dbowner
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'de-CH-x-icu'
;
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
Locale | ICU Rules | Access privileges-----------+----------+----------+-----------------+---------+------- +-------------+-----------+-----------------------eng001 | dbowner | UTF8 | icu | C | C |
de-CH-x-icu | | =CTc/dbowner +| | | | | |
| | dbowner=CTc/dbownerpostgres | postgres | UTF8 | icu | C | C |
de-CH-x-icu | |template0 | postgres | UTF8 | icu | C | C |
de-CH-x-icu | | =c/postgres +| | | | | |
| | postgres=CTc/postgrestemplate1 | postgres | UTF8 | icu | C | C |
de-CH-x-icu | | =c/postgres +| | | | | |
| | postgres=CTc/postgres(4 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com
PG18
Debian Trixie
de_CH.utf-8
Show quoted text
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Mittwoch, 5. August 2026 16:12
An: Zwettler Markus (OIZ) <markus.zwettler@zuerich.ch>; pgsql-
general@lists.postgresql.org
Betreff: [Extern] Re: ICU database?--- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von Informationen ---On 8/5/26 5:55 AM, Zwettler Markus (OIZ) wrote:
I'm creating a PG cluster with Swiss High German ICU locales.
What Postgres version?
What OS and version?
What is the OS locale?
I wonder why “collate = C” and “ctype = C” is shown?
initdb --locale-provider=icu --icu-locale=de-CH-x-icu -D /path/to/
datainitdbCREATE DATABASE eng001
WITH
OWNER = dbowner
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'de-CH-x-icu'
;
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
Locale | ICU Rules | Access privileges-----------+----------+----------+-----------------+---------+------- +-------------+-----------+-----------------------eng001 | dbowner | UTF8 | icu | C | C |
de-CH-x-icu | | =CTc/dbowner +| | | | | |
| | dbowner=CTc/dbownerpostgres | postgres | UTF8 | icu | C | C |
de-CH-x-icu | |template0 | postgres | UTF8 | icu | C | C |
de-CH-x-icu | | =c/postgres +| | | | | |
| | postgres=CTc/postgrestemplate1 | postgres | UTF8 | icu | C | C |
de-CH-x-icu | | =c/postgres +| | | | | |
| | postgres=CTc/postgres(4 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com
"Zwettler Markus (OIZ)" <markus.zwettler@zuerich.ch> writes:
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
Locale | ICU Rules | Access privileges-----------+----------+----------+-----------------+---------+------- +-------------+-----------+-----------------------eng001 | dbowner | UTF8 | icu | C | C |
de-CH-x-icu | | =CTc/dbowner +| | | | | |
| | dbowner=CTc/dbowner
I believe those columns reflect the libc locale settings that are
being used. They don't affect behavior that is driven by ICU ...
but not everything is. You would likely want to set LANG=de_DE
or the like during initdb, along with specifying the ICU locale.
regards, tom lane
My LANG setting already is "LANG=de_CH.utf-8" while I do:
initdb --locale-provider=icu --icu-locale=de-CH-x-icu -D /path/to/
Show quoted text
-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: Mittwoch, 5. August 2026 16:57
An: Zwettler Markus (OIZ) <markus.zwettler@zuerich.ch>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-
general@lists.postgresql.org
Betreff: Re: AW: [Extern] Re: ICU database?"Zwettler Markus (OIZ)" <markus.zwettler@zuerich.ch> writes:
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
Locale | ICU Rules | Access privileges-----------+----------+----------+-----------------+---------+------- +-------------+-----------+-----------------------eng001 | dbowner | UTF8 | icu | C | C |
de-CH-x-icu | | =CTc/dbowner +| | | | | |
| | dbowner=CTc/dbownerI believe those columns reflect the libc locale settings that are being used. They
don't affect behavior that is driven by ICU ...
but not everything is. You would likely want to set LANG=de_DE or the like during
initdb, along with specifying the ICU locale.regards, tom lane
On Wed, 2026-08-05 at 12:55 +0000, 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 --locale-provider=icu --icu-locale=de-CH-x-icu -D /path/to/datainitdb
CREATE DATABASE eng001
WITH
OWNER = dbowner
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'de-CH-x-icu'
;
That's because you didn't specify a C library locale, so "initdb" took the
setting from your shell environment.
Try:
initdb --locale-provider=icu --icu-locale=de-CH --locale=de_CH.utf8 -D /path/to/datainitdb
Many aspects of the locale are still taken from the C library. It's mostly the
collations that will be taken from ICU.
Yours,
Laurenz Albe
Maybe we should throw a warning if --icu-locale is set but locale is not
(or if it defaults to C)? Seems a combination nobody really wants.
--
Cheers,
Greg
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/
I am using cloudnative-pg.
I had to define everything there - even localeCollate(LC_COLLATE) and localeCType(LC_CTYPE) - to get that working:
...
initdb:
...
localeProvider: icu
icuLocale: de-CH
locale: de_CH.utf8
localeCollate: de_CH.utf8
localeCType: de_CH.utf8
encoding: UTF8
...
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
eng001 | dbowner | UTF8 | icu | de_CH.utf8 | de_CH.utf8 | de-CH | | =CTc/dbowner +
| | | | | | | | dbowner=CTc/dbowner
postgres | postgres | UTF8 | icu | de_CH.utf8 | de_CH.utf8 | de-CH | |
template0 | postgres | UTF8 | icu | de_CH.utf8 | de_CH.utf8 | de-CH | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | icu | de_CH.utf8 | de_CH.utf8 | de-CH | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
Thanks, Markus
Show quoted text
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Donnerstag, 6. August 2026 08:25
An: Zwettler Markus (OIZ) <markus.zwettler@zuerich.ch>; pgsql-
general@lists.postgresql.org
Betreff: [Extern] Re: ICU database?--- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von Informationen ---On Wed, 2026-08-05 at 12:55 +0000, 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 --locale-provider=icu --icu-locale=de-CH-x-icu -D
/path/to/datainitdbCREATE DATABASE eng001
WITH
OWNER = dbowner
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'de-CH-x-icu'
;That's because you didn't specify a C library locale, so "initdb" took the setting
from your shell environment.Try:
initdb --locale-provider=icu --icu-locale=de-CH --locale=de_CH.utf8 -D
/path/to/datainitdbMany aspects of the locale are still taken from the C library. It's mostly the
collations that will be taken from ICU.Yours,
Laurenz Albe