Thread
-
Agregates in update?
Aleksey Dashevsky <postgres@luckynet.co.il> — 1998-04-10T10:31:02Z
hi, All! Just installed PG 6.3.1 -- really great job, thank you guys! But this morning I decided to play a bit with aggregate functions on update and got a bit strange(for me, at least :-) result. Here is an exmaple of what I did: ======================================== Let's create two simple tables create table a (name text sum float); create table b (name text ,val float); --and then populate them with rows insert into a values ('bob', 0.0); insert into a values ('john', 0.0 ); insert into a values ('mike', 0.0); insert into b values ('bob', 1.0); insert into b values ('bob', 2.0); insert into b values ('bob', 3.0); insert into b values ('john', 4.0); insert into b values ('john', 5.0); insert into b values ('john', 6.0); insert into b values ('mike', 670); insert into b values ('mike', 8.0); insert into b values ('mike', 9.0); --now I want to update "sum" fields of table a in a way they will conatain --sums of field "val" from table b groupped by name --and use for this following query: update a set sum=sum(b.val) where name=b.name ; --Now select * from a; -- gives me: name|sum ----+--- john| 0 mike| 0 bob |708 (3 rows) =================== Now I'm wondering if there is reall problem in PostgreSQL or my misundersanding of something important in SQL. I'm running Linux-2.0.30(Slackware) and gcc-2.7.2.3 Thank you, Aleksey. -
escape character \
Jose' Soares Da Silva <sferac@proxy.bazzanese.com> — 1998-04-16T11:10:30Z
Seems there's a bug using ESCAPE character (\) on LIKE clause: prova=> create table tmp ( a text); CREATE prova=> insert into tmp values('\\'); INSERT 178729 1 prova=> select * from tmp where a = '\\'; a -- \\ (1 row) prova=> select * from tmp where a like '%\\%'; a - (0 rows) prova=> select * from tmp where a like '%\\\\%'; a -- \\ (1 row) -- how many \ do I have to use? 1, 2, 3, 4 or 5 ??? prova=> select * from tmp where a like '%\\\\\%'; a -- \\ (1 row) Jose' -
Re: [HACKERS] Agregates in update?
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-04-27T03:21:09Z
Added to TODO list. > > hi, All! > Just installed PG 6.3.1 -- really great job, thank you guys! > > But this morning I decided to play a bit with aggregate functions on > update and got a bit strange(for me, at least :-) result. > Here is an exmaple of what I did: > ======================================== > Let's create two simple tables > create table a (name text sum float); > create table b (name text ,val float); > > --and then populate them with rows > > insert into a values ('bob', 0.0); > insert into a values ('john', 0.0 ); > insert into a values ('mike', 0.0); > > insert into b values ('bob', 1.0); > insert into b values ('bob', 2.0); > insert into b values ('bob', 3.0); > insert into b values ('john', 4.0); > insert into b values ('john', 5.0); > insert into b values ('john', 6.0); > insert into b values ('mike', 670); > insert into b values ('mike', 8.0); > insert into b values ('mike', 9.0); > > --now I want to update "sum" fields of table a in a way they will conatain > --sums of field "val" from table b groupped by name > --and use for this following query: > update a set sum=sum(b.val) where name=b.name ; > --Now > select * from a; > -- gives me: > name|sum > ----+--- > john| 0 > mike| 0 > bob |708 > (3 rows) > > =================== > Now I'm wondering if there is reall problem in PostgreSQL or my > misundersanding of something important in SQL. > > I'm running Linux-2.0.30(Slackware) and gcc-2.7.2.3 > > Thank you, > Aleksey. > > > -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h) -
Re: [HACKERS] escape character \
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-04-27T03:34:25Z
> > Seems there's a bug using ESCAPE character (\) on LIKE clause: > > prova=> create table tmp ( a text); > CREATE > prova=> insert into tmp values('\\'); > INSERT 178729 1 > prova=> select * from tmp where a = '\\'; > a > -- > \\ > (1 row) > > prova=> select * from tmp where a like '%\\%'; > a > - > (0 rows) > > prova=> select * from tmp where a like '%\\\\%'; > a > -- > \\ > (1 row) > > -- how many \ do I have to use? 1, 2, 3, 4 or 5 ??? > > prova=> select * from tmp where a like '%\\\\\%'; > a > -- > \\ > (1 row) > Jose' The problem is that that \\ is need to input a backslash, and we support \ to escape special characters like %, so \\\\ is need to test for a backslash in a LIKE. Is this not standard? I suppose not. Should we remove the special use of \ in LIKE? Comments? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h) -
Re: [HACKERS] Agregates in update?
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-04-27T07:26:10Z
Bruce Momjian wrote: > > Added to TODO list. > > update a set sum=sum(b.val) where name=b.name ; Is this in standards ??? I thought that subselects should be used in such cases... And this is one of my plans for 6.4... Vadim
-
Re: [HACKERS] Agregates in update?
David Gould <dg@illustra.com> — 1998-04-27T08:49:10Z
Vadim wrote: > Bruce Momjian wrote: > > > > Added to TODO list. > > > update a set sum=sum(b.val) where name=b.name ; > > Is this in standards ??? > I thought that subselects should be used in such cases... > And this is one of my plans for 6.4... > > Vadim I tried this with Illustra: create table a (name text, sum float); create table b (name text, val float); --and then populate them with rows insert into a values ('bob', 0.0); ... insert into b values ('mike', 9.0); --now I want to update "sum" fields of table a in a way they will --conatain sums of field "val" from table b groupped by name --and use for this following query: update a set sum=sum(b.val) where name=b.name ; XL0002:schema b does not exist The problem of course is that the query update a set sum=sum(b.val) where name=b.name; is as Vadim points out, not valid SQL. Probably we should return an error. I am not especially thrilled with the message above about schemas, but I can see how it got there as the parser tried to find something (in the absence of a from list) to give meaning to the term 'b.*'. -dg David Gould dg@illustra.com 510.628.3783 or 510.305.9468 Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612 "(Windows NT) version 5.0 will build on a proven system architecture and incorporate tens of thousands of bug fixes from version 4.0." -- <http://www.microsoft.com/y2k.asp?A=7&B=5> -
Re: [HACKERS] escape character \
Jose' Soares Da Silva <sferac@proxy.bazzanese.com> — 1998-04-27T10:01:40Z
On Sun, 26 Apr 1998, Bruce Momjian wrote: > > > > Seems there's a bug using ESCAPE character (\) on LIKE clause: > > > > prova=> create table tmp ( a text); > > CREATE > > prova=> insert into tmp values('\\'); > > INSERT 178729 1 > > prova=> select * from tmp where a = '\\'; > > a > > -- > > \\ > > (1 row) > > > > prova=> select * from tmp where a like '%\\%'; > > a > > - > > (0 rows) > > > > prova=> select * from tmp where a like '%\\\\%'; > > a > > -- > > \\ > > (1 row) > > > > -- how many \ do I have to use? 1, 2, 3, 4 or 5 ??? > > > > prova=> select * from tmp where a like '%\\\\\%'; > > a > > -- > > \\ > > (1 row) > > Jose' > > The problem is that that \\ is need to input a backslash, and we support > \ to escape special characters like %, so \\\\ is need to test for a > backslash in a LIKE. Is this not standard? I suppose not. The LIKE standard SQL92 has the keyword ESCAPE to specify a character as escape, like this: SELECT * FROM my_table WHERE my_col LIKE '#_pluto' ESCAPE '#'; > Should we remove the special use of \ in LIKE? Comments? Obviously we need a character escape (back slash or other) to escape _ or/and %, but before remove use of back slashes we need to have the LIKE SQL92 syntax. Jose' -
Re: [HACKERS] Agregates in update?
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-04-27T13:25:37Z
> > Bruce Momjian wrote: > > > > Added to TODO list. > > > update a set sum=sum(b.val) where name=b.name ; > > Is this in standards ??? > I thought that subselects should be used in such cases... > And this is one of my plans for 6.4... No, not standard, but either need to dis-allow it, or make it work properly. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)