Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] LIKE and SIMILAR TO

From: "c k" <shreeseva(dot)learning(at)gmail(dot)com>
To: "Tino Wildenhain" <tino(at)wildenhain(dot)de>, oleg(at)sai(dot)msu(dot)su, postgres(at)tauceti(dot)net
Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Date: 2008-08-22 11:43:19
Message-ID: d8e7a1e30808220443u7df71a96pb276706af0e5d72f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
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
>

In response to

pgsql-admin by date

Next:From: Ivan Sergio BorgonovoDate: 2008-08-22 12:00:48
Subject: Re: LIKE and SIMILAR TO
Previous:From: Tino WildenhainDate: 2008-08-22 11:31:05
Subject: Re: [GENERAL] LIKE and SIMILAR TO

pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2008-08-22 12:00:48
Subject: Re: LIKE and SIMILAR TO
Previous:From: Tino WildenhainDate: 2008-08-22 11:31:05
Subject: Re: [GENERAL] LIKE and SIMILAR TO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group