Re: Inexplicable duplicate rows with unique constraint

That's why I created a virtual_string function to squeeze out everything
but alpha characters and numbers 0-9 from any varchar or text columns
that I want to use as business key columns. For example, if I have a
column named job_name, I will have a companion column named v_job_name.
The v_ column is to replicate Oracle's virtual column, since postgres
doesn't have it. You don't put any values in the v_ column directly. I
simply have a trigger on insert or update to put the value in the
v_job_name column using the virtual_string(new.job_name) function. It's
the v_job_name column that use in my unique constraint so that I avoid
any unexpected sorting. Meanwhile, my job_name column is still human
readable with whatever characters I want to see, including diacritics.

Here is my function, if you want to try it out:

create or replace function store.virtual_string(string_in text)
returns text as
$body$
declare
l_return text;
begin
l_return := regexp_replace
(lower(unaccent(string_in)),'[^0-9a-z]','','g');
return l_return;
end;
$body$
language plpgsql volatile security definer
;

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan(dot)hurst(at)brookhurstdata(dot)com
Mobile: 314-486-3261

On 2020-01-16 11: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.
>
> 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