Sample Header Ad - 728x90

Postgres sorting order not respecting default C collation?

1 vote
1 answer
214 views
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?
Asked by Richard Wheeldon (133 rep)
Nov 29, 2023, 02:24 PM
Last activity: Nov 29, 2023, 05:43 PM