Re: [GENERAL] LIKE and SIMILAR TO

Thanks to all,
I will try to use tsearch2 with some other index and then reply.
Regards,
CPK

On Fri, Aug 22, 2008 at 5:01 PM, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:

> Hi,
>
> c k wrote:
>
>> Hello all,
>> As we are migrating our ERP application from MySQL to PostgreSQL we have
>> some difficulties. One of them is use of Like and Similar to operators. We
>> often use LIKE to search a string from front-end without case sensetivity.
>> As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but
>> both are slower than LIKE and we must need case insensitivity. How can we
>> get this by increases speed. All search columns are VARCHAR(100)to
>> VARCHAR(250). Currently without index.
>>
>
> You could build an index on lower(column) and use lower(column) like ...
> this would speed up queries with exact match as well as 'foo%'
> e.g. "start with..." match.
>
> Make sure when you create the database cluster (initdb) you
> used the currect locale, otherwise lower() (and ilike) probably
> do not work as you might expect.
>
> For any more complex searches I'd recommend full text index,
> for example tsearch2.
>
> Regards
> Tino
>