Re: Inexplicable duplicate rows with unique constraint

On 16/01/2020 17:48, Tom Lane wrote:
> 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.

Thank you Tom. I should learn to read properly.

I think this very much explains the symptoms we're seeing. Thanks very
much for the detailed and very helpful answers!