Re: Make a column case insensitive

I create every time i need this, a copy of this field filled out by a
trigger
on insert and update that holds the upper (or lower for you) value of
the orginal field like this:

create table "users" (
"email" varchar(255),
...
"u_email" varchar(255)
...
);

CREATE OR REPLACE FUNCTION "public"."user_function" () RETURNS trigger AS'
BEGIN
NEW."u_email" = UPPER(NEW."email");
RETURN NEW;
END;
'LANGUAGE 'plpgsql';

CREATE TRIGGER "user_insert" BEFORE INSERT
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();

CREATE TRIGGER "user_update" BEFORE UPDATE
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();

and do a simple

select from blabla where u_email=:email

Param email = Upper(searchvalue).

This speed up any case insensitive searches.

---------------------------------------------
Thomas Wegner
Cabrio Meter - The Weather Plugin for Trillian
http://trillian.wegner24.de/cabriometer

"Rod Taylor" <pg(at)rbt(dot)ca> schrieb im Newsbeitrag
news:1091993235(dot)799(dot)15(dot)camel(at)jester(dot)(dot)(dot)
> On Fri, 2004-08-06 at 11:29, Gordon Ross wrote:
> > Is it possible to make a column case insensitive, without having to
pepper your SELECTs with lots of lower() function calls (and forgetting to
do it at times !)
>
> You could make yourself a set returning function to do this job -- but
> that is probably just as difficult to remember when selecting data out
> of the table.
>
> create function abc(text) returns set of tab as 'select * from tab where
> col = lower($1);' language sql;
>
> select * from abc('sEaRcH_VaLuE');
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>