From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | tlqmail(at)yahoo(dot)com |
Subject: | Re: Using UPPER and TRIM (INDEX usage) |
Date: | 2003-05-27 08:55:40 |
Message-ID: | 3ED3280C.2090601@rodos.fzk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Benoît Bournon | 2003-05-27 11:53:57 | How can I use a name of table in attribute of function, I try PREPARE ... |
Previous Message | DIB Walid | 2003-05-27 08:28:54 | postgresql and the industry |