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