Richard van der Hoff <rich...@matrix.org> writes:
> On 16/01/2020 17:12, Magnus Hagander wrote:
>> See https://wiki.postgresql.org/wiki/Locale_data_changes for hints on
>> which linux distros updated when.
> It seems like a plausible explanation but it's worth noting that all the
> indexed data here is (despite being in text columns), plain ascii. I'm
> surprised that a change in collation rules would change the sorting of
> such strings, and hence that it could lead to this problem. Am I naive?
Unfortunately, strings containing punctuation do sort differently
after these changes, even with all-ASCII data. The example given
on that wiki page demonstrates this.
RHEL6 (old glibc):
$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.utf8 sort
11
1-1
Fedora 30 (new glibc):
$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.utf8 sort
1-1
11
I concur with Daniel's suggestion that maybe "C" locale is
the thing to use for this data.
regards, tom lane
