pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Using UPPER and TRIM (INDEX usage)
2+ messages / 2 participants
[nested] [flat]

* Using UPPER and TRIM (INDEX usage)
@ 2003-05-26 02:48 Mintoo Lall <tlqmail@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Mintoo Lall @ 2003-05-26 02:48 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using UPPER and TRIM (INDEX usage)
@ 2003-05-27 08:55 Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Christoph Haller @ 2003-05-27 08:55 UTC (permalink / raw)
  To: pgsql-sql; +Cc: tlqmail@yahoo.com

 >
 > 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

And what happens if you re-write it as

SELECT * FROM myTable where trim(upper(fname))= 'JOHN':: character varying

Alternatively try
CREATE FUNCTION uppertrim (character varying) returns character varying as '
select upper(trim($1));
' LANGUAGE SQL IMMUTABLE ;
and then
CREATE INDEX index_fname2_myTable ON myTable USING btree (uppertrim(fname));

Regards, Christoph





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-05-27 08:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-26 02:48 Using UPPER and TRIM (INDEX usage) Mintoo Lall <tlqmail@yahoo.com>
2003-05-27 08:55 Re: Using UPPER and TRIM (INDEX usage) Christoph Haller <ch@rodos.fzk.de>

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