Re: citext like searches using index

On Mar 17, 2013, at 6:35 AM, Thorbjørn Weidemann <thorbjoern(at)weidemann(dot)name> wrote:

> Hi David,
>
> I found your email-address on http://www.postgresql.org/docs/9.2/static/citext.html. I hope it's ok to contact you this way.
> I would like to thank you for taking the time to make citext available for Postgres, and I hope you can help me with this problem.
>
> In my workplace we are considering using citext in a Progress -> Postgresql conversion that is underway. The Progress database always searches case-insensitively.
>
> Simply creating a normal btree index on a citext column makes = searches use the index, but I have not been able to create an index that can be used for searches like
> select <column> from <table> where <column> LIKE 'ide%';
>
> During this investigation I found out that even for varchar columns I have to append the varchar_pattern_ops opclass to the column when creating the index for it to be used for LIKE searches. But there is no citext_pattern_ops opclass.
>
> Is there currently any way to create an index that can be used to speed up searches like the one above?
> If not, do you have any idea how it might be implemented? Perhaps I could give it a try myself.
>
> Thank you in advance for any suggestions you might have.

I would think that text_pattern_ops would work, no?

Best,

David