Re: Inexplicable duplicate rows with unique constraint - Mailing list pgsql-general

Richard van der Hoff <richard@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