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
Last activity: Nov 29, 2023, 05:43 PM