Antw: RE: [SQL] join if there, blank if not
Henry Lafleur wrote: > ... > What I have always had trouble with, though, is if you have multiple fields > for a primary key. For example, if a customer master table also had ship-to > locations as the key and you wanted to get all customers and any orders for > that customer, in rough ANSI SQL it would be: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND > c.ship_to = o.ship_to > > then, in the union, it is not clear how to do it: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c, orders o > WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to > UNION > SELECT cust_number, ship_to, NULL AS item > FROM cust > WHERE ??? > ... I don't see any problems with multiple fields. See the following example: Outer join: SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt FROM tab_a LEFT JOIN tab_b ON (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1); Simulated outer join: SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt FROM tab_a , tab_b WHERE (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1) UNION SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, NULL FROM tab_a WHERE (tab_a.k1 NOT IN (SELECT tab_b.k1 FROM tab_b)) OR (tab_a.k2 NOT IN (SELECT tab_b.k2 FROM tab_b)); Gerhard
Re: [SQL] Bug in to_char()
On Wed, 12 Jul 2000, Brian Powell wrote: > Greetings, > > Working with PostGreSQL 7.02, I found the following problem: PM/AM is already known bug and is already fixed in current CVS. In 7.1 it will correct. Karel
Re: [SQL] join if there, blank if not
Henry Lafleur wrote: > Jacques, > > The problem with using the union in this way is that you get NULLs for a > number weather or not it has an associated record in calls. > > To do a pure outer join, it would be something like this: > > select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc > from calls c, numbers n > where c.cnumber=n.nnumber > union all > select null as cdate, null as cextn, null as cnumber, nnumber, ndesc > from numbers > WHERE nnumber NOT IN (SELECT nnumber FROM calls); > > --- > > What I have always had trouble with, though, is if you have multiple fields > for a primary key. For example, if a customer master table also had ship-to > locations as the key and you wanted to get all customers and any orders for > that customer, in rough ANSI SQL it would be: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND > c.ship_to = o.ship_to > > then, in the union, it is not clear how to do it: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c, orders o > WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to > UNION > SELECT cust_number, ship_to, NULL AS item > FROM cust > WHERE ??? > > which I never know what to do at ??? - > WHERE c.cust_number NOT IN (SELECT cust_number FROM orders) > is one choice, but this doesn't help if the ship to doesn't match. We can > get wild and try - > WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE > ship_to = cust.ship_to) > but if you go to two and three keys, what happens then? It seems like it > should work if we continue. But how efficiently does this work? > > Has anyone examined this problem? > > Thanks, > > Henry > > -Original Message- > From: Jacques Williams [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 12, 2000 9:41 AM > To: Gary Stainburn > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] join if there, blank if not > > Gary, > > What you want here is an outer join. The syntax would look something like > this: > > select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc > from calls c, numbers n > where c.cnumber=n.nnumber > union all > select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc > from numbers; > > (I haven't tried this, but it should work.) For more information on outer > joins, see Bruce Momjian's book at > http://www.postgresql.org/docs/aw_pgsql_book/ . > > Jacques Williams > > > On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote: > > Hi all, > > > > I just can't get my head round this one so I hope one of you can. > > > > I've got two tables, one holding phone calls, and another holding phone > numbers. > > > > I want to do a select where if the number in the calls table exists > > in the numbers table, the description is included otherwise the > > description field is blank. > > > > Unfortunately, using the select I've tried, if the number is not on > > the one of the tables, it's ignored. > > > > Calls table > > cdate date > > ctime time > > cextn char(3) > > cnumber x(12) > > > > Numbers table > > > > nnumber x(12) > > ndesc x(30) > > > > Select I tried. > > > > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc > > from calls c, numbers n where c.cnumber = n.nnumber; > > > > - > > Gary Stainburn. > > Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED] > > REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED] > > - > > Murphy's Laws: (327) The minute before the engineer arrives, the printer > starts working. > > - > > hi for multiple fields in the join condition, i always succeeded wit a WHERE NOT EXISTS SELECT ... subselect in the second part of the UNION. Hoing that may help (although perhaps suboptimal)
RE: [SQL] join if there, blank if not
Patrick, Thanks. For some reason, I was typing EXIST and it wasn't working--it's EXISTS! (OOPS--old habits) I was talking to Jacques about this. After comparing performance using NOT EXISTS, NOT IN, and even UNION vs. LEFT OUTER JOIN on an MS SQL 7 Server, they all run at about the same speed. (Interestingly, the MS SQL server did a similar query in 30 seconds to what took 90 seconds on the psql server. The MS SQL server is a 500MHz dual PIII with 1 GB of RAM and a RAID-5 with 4 9GB SCSI drives. The PSQL server is a Linux box busy doing many things with a 166 MHz Pentium MMX, 96 MB of RAM, and a slow IDE HD.) Henry -Original Message- From: Patrick Jacquot [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 13, 2000 5:59 AM To: [EMAIL PROTECTED] Subject: Re: [SQL] join if there, blank if not Henry Lafleur wrote: > Jacques, > > The problem with using the union in this way is that you get NULLs for a > number weather or not it has an associated record in calls. > > To do a pure outer join, it would be something like this: > > select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc > from calls c, numbers n > where c.cnumber=n.nnumber > union all > select null as cdate, null as cextn, null as cnumber, nnumber, ndesc > from numbers > WHERE nnumber NOT IN (SELECT nnumber FROM calls); > > --- > > What I have always had trouble with, though, is if you have multiple fields > for a primary key. For example, if a customer master table also had ship-to > locations as the key and you wanted to get all customers and any orders for > that customer, in rough ANSI SQL it would be: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND > c.ship_to = o.ship_to > > then, in the union, it is not clear how to do it: > > SELECT c.cust_number, c.ship_to, o.item > FROM cust c, orders o > WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to > UNION > SELECT cust_number, ship_to, NULL AS item > FROM cust > WHERE ??? > > which I never know what to do at ??? - > WHERE c.cust_number NOT IN (SELECT cust_number FROM orders) > is one choice, but this doesn't help if the ship to doesn't match. We can > get wild and try - > WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE > ship_to = cust.ship_to) > but if you go to two and three keys, what happens then? It seems like it > should work if we continue. But how efficiently does this work? > > Has anyone examined this problem? > > Thanks, > > Henry > > -Original Message- > From: Jacques Williams [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 12, 2000 9:41 AM > To: Gary Stainburn > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] join if there, blank if not > > Gary, > > What you want here is an outer join. The syntax would look something like > this: > > select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc > from calls c, numbers n > where c.cnumber=n.nnumber > union all > select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc > from numbers; > > (I haven't tried this, but it should work.) For more information on outer > joins, see Bruce Momjian's book at > http://www.postgresql.org/docs/aw_pgsql_book/ . > > Jacques Williams > > > On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote: > > Hi all, > > > > I just can't get my head round this one so I hope one of you can. > > > > I've got two tables, one holding phone calls, and another holding phone > numbers. > > > > I want to do a select where if the number in the calls table exists > > in the numbers table, the description is included otherwise the > > description field is blank. > > > > Unfortunately, using the select I've tried, if the number is not on > > the one of the tables, it's ignored. > > > > Calls table > > cdate date > > ctime time > > cextn char(3) > > cnumber x(12) > > > > Numbers table > > > > nnumber x(12) > > ndesc x(30) > > > > Select I tried. > > > > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc > > from calls c, numbers n where c.cnumber = n.nnumber; > > > > - > > Gary Stainburn. > > Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED] > > REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED] > > - > > Murphy's Laws: (327) The minute before the engineer arrives, the printer > starts working. > > - > > hi for multiple fields in the join condition, i always succeeded wit a WHERE NOT EXISTS SELECT ... subselect in the second part of the UNION. Hoing that may help (although perhaps suboptimal)
[SQL] update some elements in the array
Hello,
Could someone please tell me that is there anyway that I can update only
a few elements in a Postgres array.
eg; I have an array of text, declared as 'text[]'.
{"1","2","3","4","5"} ==> {"1","2","7","8","9"}
I want to update the last three element without affecting other
elements. I could have done it by retrieving all elements in the array
first and the do a
UPDATE table
SET arr='{"1","2","3","4","5"}'
WHERE blahblah
however, that might be troublesome, so I tried
UPDATE table
SET arr[1]=val1
arr[2]=val2...
but it didn't work. If anyone knows, please tell me. Thanks for the
help.
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
Re: [SQL] update some elements in the array
Jouni Ahto wrote:
> On Thu, 13 Jul 2000, Bernie Huang wrote:
>
> > I want to update the last three element without affecting other
> > elements. I could have done it by retrieving all elements in the array
> > first and the do a
> >
> > UPDATE table
> > SET arr='{"1","2","3","4","5"}'
> > WHERE blahblah
> >
> > however, that might be troublesome, so I tried
> >
> > UPDATE table
> > SET arr[1]=val1
> > arr[2]=val2...
> >
> > but it didn't work. If anyone knows, please tell me. Thanks for the
> > help.
>
> What does "it didn't work" in this case exactly mean?
>
> Did some testing with postgres, results;
>
> create table test (arr text[]);
> insert into test values ('{"1","2","3","4","5"}');
> select * from test;
> arr
> ---
> {"1","2","3","4","5"}
> (1 row)
> update test set arr[4]='8', arr[2]='9';
> select * from test;
> arr
> ---
> {"1","9","3","8","5"}
> (1 row)
>
> So, updating arrays definitely works. Are you having a syntax error
> somewhere or what?
>
> -- Jouni Ahto
Thanks. I forgot the quote...' '
(Man, that quote just drives me nuts...)
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
Re: [SQL] update some elements in the array
Bernie Huang <[EMAIL PROTECTED]> writes:
> however, that might be troublesome, so I tried
> UPDATE table
> SET arr[1]=val1
> arr[2]=val2...
> but it didn't work.
Hmm, probably not ... I don't see any easy way around that, since
the array elements aren't really separate fields. Under the hood
this isn't much different from
UPDATE table SET arr = something, arr = somethingElse
and the best we could really do for you is issue an error message
noting that only one of the assignments is going to win.
You can update several adjacent elements in the array with a slice
assignment, if that helps:
regression=# create table ff (a int[]);
CREATE
regression=# insert into ff values('{1,2,3,4,5,6}');
INSERT 399882 1
regression=# select * from ff;
a
---
{1,2,3,4,5,6}
(1 row)
regression=# update ff set a[4:5] = '{44,55}';
UPDATE 1
regression=# select * from ff;
a
-
{1,2,3,44,55,6}
(1 row)
But if the elements aren't adjacent you'll have to do multiple UPDATEs.
regards, tom lane
