From: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
---|---|
To: | "Psql_General (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Pavel Křehula <pavel(dot)krehula(at)nlm(dot)cz> |
Subject: | Re: Case Insensitive Comparison with Postgres 12 |
Date: | 2019-10-10 12:41:47 |
Message-ID: | 9803fc4d-7fe4-33f3-014e-defdfcc59873@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Kabaivanov | 2019-10-10 12:51:34 | syntax error with v12 |
Previous Message | Luca Ferrari | 2019-10-10 11:58:35 | Re: Minimum privilege for Backup and replication |