Re: citext LIKE search bug - Mailing list pgsql-bugs

From Jeff Janes
Subject Re: citext LIKE search bug
Date
Msg-id CAMkU=1xSXBObcZX3-mjFOdupZA7PEHcqcqgmCA_SocsvLabzBg@mail.gmail.com
Whole thread Raw
In response to Re: citext LIKE search bug  (Morris de Oryx <morrisdeoryx@gmail.com>)
Responses Re: citext LIKE search bug
List pgsql-bugs


On Thu, Sep 19, 2019 at 12:30 AM Morris de Oryx <morrisdeoryx@gmail.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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16016: deadlock with startup process, AccessExclusiveLock on pg_statistic's toast table
Next
From: Andrew Gierth
Date:
Subject: Re: BUG #16017: Postgres does not respond non-local connections