X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id BA7B5925921
	for <pgsql-sql@postgresql.org>; Sun, 25 May 2003 22:48:31 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new) with ESMTP id 24117-04 for <pgsql-sql@postgresql.org>;
	Sun, 25 May 2003 22:48:30 -0400 (EDT)
Received: from web40204.mail.yahoo.com (web40204.mail.yahoo.com
 [66.218.78.65])
	by developer.postgresql.org (Postfix) with SMTP id 8FC7B925914
	for <pgsql-sql@postgresql.org>; Sun, 25 May 2003 22:48:29 -0400 (EDT)
Message-ID: <20030526024829.32935.qmail@web40204.mail.yahoo.com>
Received: from [66.139.15.154] by web40204.mail.yahoo.com via HTTP;
	Sun, 25 May 2003 19:48:29 PDT
Date: Sun, 25 May 2003 19:48:29 -0700 (PDT)
From: Mintoo Lall <tlqmail@yahoo.com>
Subject: Using UPPER and TRIM (INDEX usage)
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1520874341-1053917309=:32785"
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/493
X-Sequence-Number: 13658

--0-1520874341-1053917309=:32785
Content-Type: text/plain; charset=us-ascii

Hi Everybody,
 
How do I use "TRIM" and "UPPER" both in the SQL statement and still use the index.
 
I created an index on myTable in the following way
 
CREATE INDEX index_fname_myTable ON myTable USING btree (upper(fname));

Now  the SQL I used was
 

SELECT * FROM myTable where upper(trim(fname))= 'JOHN':: character varying
 
The postgresql doesnt use the index on fname in the above case. 
 
But if I use only "UPPER" in the SQL statement, the postgresql uses the index. For eg. SELECT * FROM myTable where upper(fname)= 'JOHN':: character varying
 
Any help is appreciated. 
 
Regards
Mintoo
 
 
 
 


---------------------------------
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
--0-1520874341-1053917309=:32785
Content-Type: text/html; charset=us-ascii

<DIV>Hi Everybody,</DIV>
<DIV>&nbsp;</DIV>
<DIV>How do I use&nbsp;"TRIM" and&nbsp;"UPPER" both in the SQL statement and still use the index.</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV>I created an index on myTable in the following way</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE INDEX index_fname_myTable ON&nbsp;myTable USING btree (upper(fname));<BR></DIV>
<DIV>Now&nbsp; the SQL I used was</DIV>
<DIV>&nbsp;</DIV></DIV>
<DIV>SELECT * FROM&nbsp;myTable where upper(trim(fname))= 'JOHN':: character varying</DIV>
<DIV>&nbsp;</DIV>
<DIV>The postgresql doesnt use the index on fname in the above case.&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>But if I use only "UPPER" in the SQL statement, the postgresql uses&nbsp;the index. For eg. SELECT * FROM&nbsp;myTable where upper(fname)= 'JOHN':: character varying</DIV>
<DIV>&nbsp;</DIV>
<DIV>Any help is appreciated. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Regards</DIV>
<DIV>Mintoo</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com">The New Yahoo! Search</a> - Faster. Easier. Bingo.
--0-1520874341-1053917309=:32785--