Make a column case insensitive
Make a column case insensitive
am 06.08.2004 17:29:19 von G.Ross
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Re: Make a column case insensitive
am 08.08.2004 21:27:15 von pg
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
Re: Make a column case insensitive
am 08.08.2004 22:39:29 von Thomas Wegner
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" 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
>
Re: Make a column case insensitive
am 09.08.2004 05:40:35 von JanWieck
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 operators that 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 #
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Re: Make a column case insensitive
am 09.08.2004 08:09:05 von lists
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.
---------------------------(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
Re: Make a column case insensitive
am 09.08.2004 08:21:16 von tgl
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly