Re: Case Insensitive Comparison with Postgres 12

On Fri, Oct 11, 2019 at 1:09 AM stan <stanb(at)panix(dot)com> wrote:

> On Thu, Oct 10, 2019 at 05:41:47AM -0700, Igal @ Lucee.org wrote:
> > 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?
> >
> 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.

Igal