1

I have a postgres DB running in an AL2023 docker image:

postgres=# select version();
select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 row)

postgres=#

I created this using these args:

--locale-provider=icu --icu-locale=C.UTF8 --encoding=UTF8

Which produces the following:

The database cluster will be initialized with this locale configuration:
  provider:    icu
  ICU locale:  C.UTF8
  LC_COLLATE:  C
  LC_CTYPE:    C
  LC_MESSAGES: C
  LC_MONETARY: C
  LC_NUMERIC:  C
  LC_TIME:     C
The default text search configuration will be set to "english".

If I ask for a sorting between caps and noncaps, I get lower case coming first which (a) isn't what I want and (b) isn't what I'd expect for ASCII / UTF-8 / C

postgres=# select 'C' < 'c';
select 'C' < 'c';
 ?column? 
----------
 f
(1 row)

So if I used the C collation, I'd get it the right way round, perhaps?

postgres=# select 'C' < 'c' collate "C";
select 'C' < 'c' collate "C";
 ?column? 
----------
 t
(1 row)

I thought I was using C, so I check what collation am I using and get ...

postgres=# select datname, datcollate from pg_database;
select datname, datcollate from pg_database;
  datname  | datcollate 
-----------+------------
 postgres  | C
 template1 | C
 template0 | C
(3 rows)

I'm using C - so why is the default not the same as if I use C explicitly?

1
  • 1
    Note that starting in v16, this fails with "could not convert locale name "C.UTF8" to language tag" so validation has improved since v15.
    – jjanes
    Nov 29, 2023 at 18:44

1 Answer 1

1

C.UTF8 is not a valid ICU collation. If you give ICU a garbage collation name like that, it ignores what it cannot parse, and you probably end up with the und collation instead. That would explain what you are seeing.

If you want the binary collation, do not use ICU collations, but the default C library collation:

initdb --encoding=UTF8 --licale-provider=libc --locale=POSIX datadir
2
  • Yup. Thanks. That's the problem. If I do --locale=potatoes I get (as I'd expect) an error. If I do --icu-locale=potatoes it (for some reason) doesn't error, it just appears to create the DB successfully (but wrongly). Nov 29, 2023 at 21:24
  • ICU accepts the name without error, so PostgreSQL cannot tell. Nov 30, 2023 at 6:20

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.