From: c k <shreeseva.learning@gmail.com>
To: Tino Wildenhain <tino@wildenhain.de>
To: oleg@sai.msu.su
To: postgres@tauceti.net
Cc: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Date: Fri, 22 Aug 2008 17:13:19 +0530
Message-ID: <d8e7a1e30808220443u7df71a96pb276706af0e5d72f@mail.gmail.com> (raw)
In-Reply-To: <48AEA379.7080807@wildenhain.de>
References: <d8e7a1e30808220413s3e7c593fr7ee2be6af70ff1ca@mail.gmail.com>
<48AEA379.7080807@wildenhain.de>
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@wildenhain.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: shreeseva.learning@gmail.com, tino@wildenhain.de, oleg@sai.msu.su, postgres@tauceti.net, pgsql-general@postgresql.org
Subject: Re: [GENERAL] LIKE and SIMILAR TO
In-Reply-To: <d8e7a1e30808220443u7df71a96pb276706af0e5d72f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox