Re: Using UPPER and TRIM (INDEX usage)

Поиск
Список
Период
Сортировка
От Christoph Haller
Тема Re: Using UPPER and TRIM (INDEX usage)
Дата
Msg-id 3ED3280C.2090601@rodos.fzk.de
обсуждение исходный текст
Ответ на Using UPPER and TRIM (INDEX usage)  (Mintoo Lall <tlqmail@yahoo.com>)
Список pgsql-sql
>> 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




В списке pgsql-sql по дате отправления:

Предыдущее
От: "Mukta A. Telang"
Дата:
Сообщение: Re: Many-to-Many relation
Следующее
От: "Eric Anderson Vianet SAO"
Дата:
Сообщение: discover a toast table name