Обсуждение: Case Insensitive Comparison with Postgres 12

Igal Sapir wrote:
> I am trying to test a simple case insensitive comparison.  Most likely the
> collation that I chose is wrong, but I'm not sure how to choose the correct
> one (for English/US?).  Here is my snippet:
> 
> create collation case_insensitive(
>     provider=icu, locale='en-US-x-icu', deterministic=false
> );
> select 'Abc' = 'abc' collate case_insensitive;
> 
> I expected true but am getting false.
> 
> Any thoughts?

Yes, the LOCALE is wrong. Use

create collation case_insensitive (
   provider=icu, locale='en-US-u-ks-level2', deterministic=false
);

The name of the locale defines it.

My blog post can give a simple introduction:
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 10/9/2019 12:34 AM, Laurenz Albe wrote:
> Igal Sapir wrote:
>> I am trying to test a simple case insensitive comparison.  Most likely the
>> collation that I chose is wrong, but I'm not sure how to choose the correct
>> one (for English/US?).  Here is my snippet:
>>
>> create collation case_insensitive(
>>      provider=icu, locale='en-US-x-icu', deterministic=false
>> );
>> select 'Abc' = 'abc' collate case_insensitive;
>>
>> I expected true but am getting false.
>>
>> Any thoughts?
> Yes, the LOCALE is wrong. Use
>
> create collation case_insensitive (
>     provider=icu, locale='en-US-u-ks-level2', deterministic=false
> );
>
> The name of the locale defines it.
>
> My blog post can give a simple introduction:
> https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

Thank you all for replying.  I tried to use the locale suggested by both 
Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting false for a 
simple comparison of 'Abc' = 'abc'.  I tried the locale both as a 
'string' and as an "identifier":

 > select version();

version |
-------------------------------------------------------------------------------------------------------|
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-39), 64-bit|

 > drop collation if exists case_insensitive;

 > create collation case_insensitive (
    provider=icu, locale="en-US-u-ks-level2", deterministic=false
);

 > select 'Abc' = 'abc' collate case_insensitive as is_equal;

is_equal|
--------|
false   |

What am I doing wrong here?

Thanks,

Igal

Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:
> Thank you all for replying.  I tried to use the locale suggested by
> both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
> false for a simple comparison of 'Abc' = 'abc'.  I tried the locale
> both as a 'string' and as an "identifier":
> 
>> drop collation if exists case_insensitive;
> 
>> create collation case_insensitive (
>    provider=icu, locale="en-US-u-ks-level2", deterministic=false
> );
> 
>> select 'Abc' = 'abc' collate case_insensitive as is_equal;
> 
> is_equal|
> --------|
> false   |
> 
> What am I doing wrong here?

Check the version of libicu that your Linux is using. 
That locale format requires version 54 or later.
(My up-to-date CentOS 7.7 for example is still using version 50 and the EDB Windows binaries include version 53). 

In another thread about ICU problems, Daniel Verite explained that in more detail:

> With ICU 53 or older, instead of the locale above, we must use the old-style syntax:
> 
>  locale = 'de-DE@colStrength=secondary'

In your case I guess, it should be 

   locale = 'en-US@colStrength=secondary'

Thomas

Thomas,

On 10/10/2019 6:22 AM, Thomas Kellerer wrote:

> Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:
>> Thank you all for replying.  I tried to use the locale suggested by
>> both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
>> false for a simple comparison of 'Abc' = 'abc'.  I tried the locale
>> both as a 'string' and as an "identifier":
>>
>>> drop collation if exists case_insensitive;
>>> create collation case_insensitive (
>>     provider=icu, locale="en-US-u-ks-level2", deterministic=false
>> );
>>
>>> select 'Abc' = 'abc' collate case_insensitive as is_equal;
>> is_equal|
>> --------|
>> false   |
>>
>> What am I doing wrong here?
> Check the version of libicu that your Linux is using.
> That locale format requires version 54 or later.
> (My up-to-date CentOS 7.7 for example is still using version 50 and the EDB Windows binaries include version 53).
>
> In another thread about ICU problems, Daniel Verite explained that in more detail:
>
>> With ICU 53 or older, instead of the locale above, we must use the old-style syntax:
>>
>>   locale = 'de-DE@colStrength=secondary'
> In your case I guess, it should be
>
>     locale = 'en-US@colStrength=secondary'

That works, thank you!

I also have CentOS installed on that machine: CentOS Linux release 
7.7.1908 (Core), showing libicu Version 50.2 via `yum info libicu`.

Best,

Igal

    Igal Sapir wrote:

> > Out of curiosity is there a eason not to use the citext type for th?
> >
> >
> Using the collation seems like a much cleaner approach, and I trust ICU to
> do a better job at comparing strings according to language rules etc.

One notable difference between citext and case-insensitive collations
by ICU is that the latter recognizes canonically equivalent sequences
of codepoints [1] as equal, while the former does not.

For instance:

=# CREATE COLLATION ci (locale='und@colStrength=secondary',
      provider='icu', deterministic=false);

=# SELECT E'E\u0302TES'::citext = 'Êtes'::citext AS "citext-equal",
      E'E\u0302TES' = 'Êtes' collate "ci" AS "ci-equal";
 citext-equal | ci-equal
--------------+----------
 f          | t

Another significant difference is that building or rebuilding an index on a
text column with a CI collation appears to be way faster than with citext
(I've seen 10:1 ratios, but do your own tests).

On the minus side, substring matching with LIKE or other methods
is not possible with CI collations whereas it does work with citext.


[1] https://en.wikipedia.org/wiki/Unicode_equivalence


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite