0

UPDATE: after initdbing with libc local provider (instead of icu), it started to work fine. Is this a bug or ICU simply assigns something else to "C"?

I created a database explicitly with:

encoding = 'utf8'
lc_collate = 'C'
lc_ctype = 'en_US.utf8'

And show lc_collate shows C. But when comparing strings, it's something else:

select 'B' > 'a';
> true

select 'B' collate "C" > 'a' collate "C";
> false

How come? The values in my columns are also sorted incorrectly unless I explicitly tell it to use "C".

Environment: MacOS, PGv15. My cluster was initialized with ICU enabled.

1 Answer 1

2

Q: ICU simply assigns something else to "C"

"C" as a locale name refers to a built-in locale in libc, but indeed it means nothing to ICU.

If you had passed "C" in the ICU_LOCALE argument to CREATE DATABASE it would have been essentially ignored, and interpreted as "unspecified language".

The purpose of passing something in the lc_collate argument of a database whose locale provider is ICU is to set the environment variable LC_COLLATE to that value. It might be useful for code in extensions, especially in PL languages, that call functions like strcoll or strxfrm. But string comparisons initiated by Postgres itself with the default ICU collation will not use what's in the lc_collate database parameter. This is why setting lc_collate to C has no effect in your new database.

To create a database with C as the default collation, in a initialized cluster with ICU as the default, it must be a libc database and created from template0 with a statement like:

CREATE DATABASE db
 TEMPLATE='template0'
 LC_COLLATE='C'
 LC_CTYPE='en_US.utf8'
 LOCALE_PROVIDER='libc';
2
  • Do you know if there's a way to find out which LOCALE_PROVIDER is used? Simple show LOCALE_PROVIDER doesn't show it.. Commented Mar 31, 2023 at 13:40
  • 1
    @StanislavBashkyrtsev: select datlocprovider from pg_database where datname=current_database(); Commented Mar 31, 2023 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.