Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 5290C650058
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri, 22 Aug 2008 08:43:25 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 51172-09 for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri, 22 Aug 2008 08:43:16 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.228])
	by postgresql.org (Postfix) with ESMTP id 4BA6364FCF8
	for <pgsql-admin@postgresql.org>; Fri, 22 Aug 2008 08:43:21 -0300 (ADT)
Received: by qb-out-0506.google.com with SMTP id d8so679650qbc.23
	for <pgsql-admin@postgresql.org>; Fri, 22 Aug 2008 04:43:19 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=gkPOgy3Pj/ZksTTJqn0aZzofcwRGMZsEwfflizZ8C3s=;
	b=aLssKjOTc+lmTWP9tACGQaKvoFQY3UK/jKb0KxG22yzHKW4L7nrg4F+MnZAMSeLFV0
	OZIpi+B+61qAPnBoNkFWBsFDXnJIsg3vZqVF1AaSFKPzEl+pLjtjcL+kU3MQvZ01g+ua
	GUky8QA1c1dUylKM2OfJ3WSVPgmI+EUiAiJlE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=pzvmd8w3U6ewCcvKfsPKBxUWSwq2oGC+EhBd+TP8D4Wm3fEZc5cp1qloS52Ttmz12R
	lDtY3K+PJcSF6e1yVHJjZlG1ESwTmwvoaIq0jvIEHYP3FtIhEEYejV8oustJaLcffW9m
	hUTLMGtw10aChPTp56J94kvXN8MACmz7S9CzU=
Received: by 10.64.253.3 with SMTP id a3mr819444qbi.40.1219405399508;
	Fri, 22 Aug 2008 04:43:19 -0700 (PDT)
Received: by 10.65.200.5 with HTTP; Fri, 22 Aug 2008 04:43:19 -0700 (PDT)
Message-ID: <d8e7a1e30808220443u7df71a96pb276706af0e5d72f@mail.gmail.com>
Date: Fri, 22 Aug 2008 17:13:19 +0530
From: "c k" <shreeseva.learning@gmail.com>
To: "Tino Wildenhain" <tino@wildenhain.de>, oleg@sai.msu.su,
	postgres@tauceti.net
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org
In-Reply-To: <48AEA379.7080807@wildenhain.de>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_32962_12466690.1219405399578"
References: <d8e7a1e30808220413s3e7c593fr7ee2be6af70ff1ca@mail.gmail.com>
	<48AEA379.7080807@wildenhain.de>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200808/118
X-Sequence-Number: 29963

------=_Part_32962_12466690.1219405399578
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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
>

------=_Part_32962_12466690.1219405399578
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr">Thanks to all, <br>I will try to use tsearch2 with some other index and then reply.<br>Regards,<br>CPK<br><br><div class="gmail_quote">On Fri, Aug 22, 2008 at 5:01 PM, Tino Wildenhain <span dir="ltr">&lt;<a href="mailto:tino@wildenhain.de">tino@wildenhain.de</a>&gt;</span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Hi,<div class="Ih2E3d"><br>
<br>
c k wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hello all,<br>
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&#39;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.<br>

</blockquote>
<br></div>
You could build an index on lower(column) and use lower(column) like ...<br>
this would speed up queries with exact match as well as &#39;foo%&#39;<br>
e.g. &quot;start with...&quot; match.<br>
<br>
Make sure when you create the database cluster (initdb) you<br>
used the currect locale, otherwise lower() (and ilike) probably<br>
do not work as you might expect.<br>
<br>
For any more complex searches I&#39;d recommend full text index,<br>
for example tsearch2.<br>
<br>
Regards<br><font color="#888888">
Tino<br>
</font></blockquote></div><br></div>

------=_Part_32962_12466690.1219405399578--