Re: [SQL] Make a column case insensitive
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
[SQL] UNIQUE constraint
Hi, I have a question about the UNIQUE constraint. The documentation describes this example: CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); But it is not clean to me. Does the above example mean that the list of pairs must be unique or is it only a short version for this constraint: CREATE TABLE example ( a integer UNIQUE, b integer, c integer UNIQUE ); Does the following table fullfill the UNIQUE clause of the example from the Postgres documentation? a b c - 1 2 3 1 1 1 cu Sascha -- secunet Security Networks AG, Im Teelbruch 116, 45219 Essen Tel: +49-2054-123-408 Fax: +49-2054-123-123 PGP: FBE2 A49B 6526 C1B4 7F10 24E4 5004 7C27 6E9A 9698 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] select
I have a table with fields: id A B C D E F where id is an int4 primary key. In this table there is information like: 1 a1 b1 xxx xxx 2 a1 b1 xxx xxx xxx xxx 3 a2 b2 xxx xxx xxx xxx 4 a2 b2 xxx xxx xxx xxx 5 a3 b3 xxx xxx xxx 6 a3 b3 xxx xxx xxx xxx 7 a3 b3 xxx xxx xxx xxx 8 a4 b4 xxx xxx xxx xxx 9 a1 b1 xxx 10 a3 b3 xxx 11 a1 b3 xxx where xxx represents any information. My problem is: I only want to select one of the records which have the same combination of information in fields A and B, and that should be the record with the lowest id. Thus, the resultset should be: 1 a1 b1 xxx xxx 3 a2 b2 xxx xxx xxx xxx 5 a3 b3 xxx xxx xxx 8 a4 b4 xxx xxx xxx xxx 11 a1 b3 xxx Importantly, I might not want the selected records listed in order of increasing id. They might be sorted according to the data in e.g. the C field. Suggestions/comments greatly appreciated. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 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" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> 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
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] UNIQUE constraint
On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote: CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); But it is not clean to me. Does the above example mean that the list of pairs must be unique Yes. Does the following table fullfill the UNIQUE clause of the example from the Postgres documentation? a b c - 1 2 3 1 1 1 Yes. For example, test=# create table example (a integer, b integer, c integer, unique (a,c)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "example_a_key" for table "example" CREATE TABLE test=# insert into example (a,b,c) values (1,2,3); INSERT 5935749 1 test=# insert into example (a,b,c) values (1,1,1); INSERT 5935750 1 test=# insert into example (a,b,c) values (1,3,3); ERROR: duplicate key violates unique constraint "example_a_key" test=# select a,b,c from example; a | b | c ---+---+--- 1 | 2 | 3 1 | 1 | 1 (2 rows) Michael Glaesemann grzm myrealbox com ---(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: [SQL] surrogate key or not?
Kenneth, > but why would anyone want to change the value of an autogenerated serial row? But if you're using a real key, it may need to change. The only reason *not* do do it that way is performance issues with CASCADE. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Make a column case insensitive
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 [EMAIL PROTECTED] -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Make a column case insensitive
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 [EMAIL PROTECTED] 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: [SQL] Make a column case insensitive
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
