Re: citext LIKE search bug

On Thu, Sep 19, 2019 at 12:30 AM Morris de Oryx <morrisdeoryx(at)gmail(dot)com>
wrote:

> Thanks for the answer, "doc bug" works for me.
>
> For what it's worth, the citext_pattern_ops option seems to provide
> case-blind = searches, which text_pattern_ops does not.
>

That is correct, but the default operator for citext type already provides
case-blind =. The only improvement on that that citext_pattern_ops could
hope to achieve is fast case-blind prefix matching, which it fails to do.

Note that you can get case-blind LIKE matching use ILIKE, and can
accelerate it with a pg_trgm index. However, if the only type of matching
you want to accelerate is prefix matching (% only at the end of the
pattner), then pg_trgm will be much less efficient than a
fully-functioning citext_pattern_ops would have been had it done what its
name implies. Still, it might be better than the other choices you
currently have.

Cheers,

Jeff