Thread: Make a column case insensitive

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 !) 

(I'm on 7.4.3)

Thanks,

GTG

Gordon Ross,
Network Manager/Rheolwr Rhydwaith
Countryside Council for Wales/Cyngor Cefn Gwlad Cymru

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');

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
>

On 8/6/2004 11:29 AM, 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 !)
 
> 
> (I'm on 7.4.3)

With a little bit of legwork you can create an itext data type. It would 
just use textin() and textout() for the data type declaration itself. From there you'd define a bunch of comparision
operatorsthat are based 
 
on sql functions doing case insensitive comparision, plus an operator 
class and a couple of implicit casts. I don't think you'd even need a 
single line of C code for that new data type.


Jan


> 
> Thanks,
> 
> GTG
> 
> Gordon Ross,
> Network Manager/Rheolwr Rhydwaith
> Countryside Council for Wales/Cyngor Cefn Gwlad Cymru
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

When I use UTF-8 encoding for my database. upper and lower() functions  
break (no longer process accented chars correctly). This is with the  
correct encoding fr-FR@utf-8 I think, for CTYPES et al.

Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> writes:
>     When I use UTF-8 encoding for my database. upper and lower() functions  
> break (no longer process accented chars correctly).

This is fixed for 8.0.
        regards, tom lane