Re: Case Insensitive Comparison with Postgres 12

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(at)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