From: "Thomas Wegner" <tomaten@t-online.de>
Subject: Re: Make a column case insensitive
Date: Sun, 8 Aug 2004 22:39:29 +0200
Organization: Hub.Org Networking Services
Lines: 64
Message-ID: <cf6324$1mi3$1@news.hub.org>
References: <s113b1e8.083@groupwise.ccw.gov.uk>
	<1091993235.799.15.camel@jester>
To: pgsql-sql@postgresql.org

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@rbt.ca> schrieb im Newsbeitrag
news:1091993235.799.15.camel@jester...
> 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
>