From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard van der Hoff <richard(at)matrix(dot)org> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inexplicable duplicate rows with unique constraint |
Date: | 2020-01-16 17:48:06 |
Message-ID: | 3248.1579196886@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Richard van der Hoff <richard(at)matrix(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard van der Hoff | 2020-01-16 18:06:04 | Re: Inexplicable duplicate rows with unique constraint |
Previous Message | Richard van der Hoff | 2020-01-16 17:31:38 | Re: Inexplicable duplicate rows with unique constraint |