From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: tlqmail@yahoo.com
Subject: Re: Using UPPER and TRIM (INDEX usage)
Date: Tue, 27 May 2003 10:55:40 +0200
Message-ID: <3ED3280C.2090601@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, tlqmail@yahoo.com
Subject: Re: Using UPPER and TRIM (INDEX usage)
In-Reply-To: <3ED3280C.2090601@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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