Thread: Agregates in update?
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.
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'
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)
>
> 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)
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
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'
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>
> > 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)