Search Postgresql Archives

Re: Case Insensitive Comparison with Postgres 12

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Oct 11, 2019 at 1:09 AM stan <stanb@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux