Thread: Using UPPER and TRIM (INDEX usage)
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.
>> 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