Thread: \copy in Current CVS
Simple script: CREATE TABLE test(a int); \copy test from '/tmp/wow' select * from test; now produce error: \copy: COPY state must be terminated first I missed something? -- Teodor Sigaev teodor@stack.net
I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant <set clause> ::=<update target> <equals operator> <row value designator> <update target> ::=<object column>| <left paren> <object column list> <right paren> or can someone think of another way? -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Bruce, Can you chime in with your support here? Dave I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant <set clause> ::=<update target> <equals operator> <row value designator> <update target> ::=<object column>| <left paren> <object column list> <right paren> or can someone think of another way? -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Dave Cramer writes:
> update table set (col...) = ( val...)
> It would appear that this is SQL3 compliant
>
> <set clause> ::=
> <update target> <equals operator> <row value designator>
>
> <update target> ::=
> <object column>
> | <left paren> <object column list> <right paren>
That's not what my copy says.
<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<set clause> ::= <update target> <equals operator> <update source> | <mutated set
clause><equals operator> <update source>
<update target> ::= <object column> | ROW | <object column>
<left bracket or trigraph> <simple value specification> <right bracket or trigraph>
<object column> ::= <column name>
<mutated set clause> ::= <mutated target> <period> <method name>
<mutated target> ::= <object column> | <mutated set clause>
<update source> ::= <value expression> | <contextually typed value specification>
(And I'm pretty sure I have the right version of the standard.)
--
Peter Eisentraut peter_e@gmx.net
Dave Cramer <dave@fastcrypt.com> writes:
> It would appear that this is SQL3 compliant
> <set clause> ::=
> <update target> <equals operator> <row value designator>
> <update target> ::=
> <object column>
> | <left paren> <object column list> <right paren>
I see no parentheses allowed there in the SQL99 spec. Encourage your
customer to use standard syntax.
regards, tom lane
Peter, Referring to http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt the following grammar exists is the reference above valid? as for tom's reply there are left paren, and right paren. Dave On Wed, 2003-02-19 at 10:37, Peter Eisentraut wrote: > Dave Cramer writes: > > > update table set (col...) = ( val...) > > > It would appear that this is SQL3 compliant > > > > <set clause> ::= > > <update target> <equals operator> <row value designator> > > > > <update target> ::= > > <object column> > > | <left paren> <object column list> <right paren> > > That's not what my copy says. > > <set clause list> ::= > <set clause> [ { <comma> <set clause> }... ] > > <set clause> ::= > <update target> <equals operator> <update source> > | <mutated set clause> <equals operator> <update source> > > <update target> ::= > <object column> > | ROW > | <object column> > <left bracket or trigraph> <simple value specification> <right bracket or trigraph> > > <object column> ::= <column name> > > > <mutated set clause> ::= > <mutated target> <period> <method name> > > <mutated target> ::= > <object column> > | <mutated set clause> > > <update source> ::= > <value expression> > | <contextually typed value specification> > > (And I'm pretty sure I have the right version of the standard.) -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Dave Cramer <dave@fastcrypt.com> writes: > Referring to > http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt > the following grammar exists > is the reference above valid? Sep 93? That would be an extremely early draft of what eventually became SQL99. Looks like the parens got lost again by the time of the final spec. Given that there's no visible functionality gain from allowing parens here, I'm not surprised that the spec authors decided it wasn't such a hot idea after all... too bad Informix didn't get the word :-( regards, tom lane
Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? Dave On Wed, 2003-02-19 at 12:29, Tom Lane wrote: > Dave Cramer <dave@fastcrypt.com> writes: > > Referring to > > http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt > > the following grammar exists > > is the reference above valid? > > Sep 93? That would be an extremely early draft of what eventually became > SQL99. Looks like the parens got lost again by the time of the final > spec. > > Given that there's no visible functionality gain from allowing parens > here, I'm not surprised that the spec authors decided it wasn't such > a hot idea after all... too bad Informix didn't get the word :-( > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Dave Cramer <dave@fastcrypt.com> writes:
> Ok, if a patch were submitted to the parser to allow the syntax in
> question would it be considered?
I would vote against it ... but that's only one vote.
regards, tom lane
On Wed, Feb 19, 2003 at 07:31:35AM -0500, Dave Cramer wrote: > Bruce, > > Can you chime in with your support here? > > Dave > > I have a large customer who is converting from informix to postgres and > they have made extensive use of > > update table set (col...) = ( val...) > > as a first pass would it be possible to translate this in the parser to > > update table set col=val > > It would appear that this is SQL3 compliant > > <set clause> ::= > <update target> <equals operator> <row value designator> > > <update target> ::= > <object column> > | <left paren> <object column list> <right paren> > > > or can someone think of another way? I don't understand the original problem. What does informix give you? A text file full of "update table set ()=()" which you then try to feed into postgres? In that case, why not pass said text file through a sed or perl script first? Cheers, Patrick
Patrick, No, they support the syntax: update table set (col1, col2, col3) = ( val1, val2, val3 ) I have a customer with a rather large application which uses this syntax, because they were using informix. There is also a rather interesting 4GL project called aubit which is on sourceforge. They would also like to see this supported for the same reasons. Dave On Wed, 2003-02-19 at 15:02, Patrick Welche wrote: > > I have a large customer who is converting from informix to postgres and > > they have made extensive use of > > > > update table set (col...) = ( val...) > > > > as a first pass would it be possible to translate this in the parser to > > > > update table set col=val > > > > It would appear that this is SQL3 compliant > > > > <set clause> ::= > > <update target> <equals operator> <row value designator> > > > > <update target> ::= > > <object column> > > | <left paren> <object column list> <right paren> > > > > > > or can someone think of another way? > > I don't understand the original problem. What does informix give you? A > text file full of "update table set ()=()" which you then try to feed > into postgres? In that case, why not pass said text file through a sed or > perl script first? > > Cheers, > > Patrick -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote: > I have a customer with a rather large application which uses this > syntax, because they were using informix. There is also a rather > interesting 4GL project called aubit which is on sourceforge. They would > also like to see this supported for the same reasons. Hey - I was going to say that... For the curious: Quick URL - http://aubit4gl.sourceforge.net/ Its a 'clone' of the Informix 4GL tool, a nice 'clean' language specifically designed for writing database applications, with both curses & GTK, support for multiple database types and a bunch of other things... We're about to release version 0.30 - and I was going to wait until then....
While I don't see the syntax of: update table set (col...) = ( val...) as valuable compared to separate col=val assignments, I do see a value in allowing subqueries in such assignments: update table set (col...) = ( select val ..) Without it, you have to do separate subquery statements, and if they are complex, that is a waste. I assume that was the motivation for the feature. --------------------------------------------------------------------------- Dave Cramer wrote: > Patrick, > > No, they support the syntax: > > update table set (col1, col2, col3) = ( val1, val2, val3 ) > > I have a customer with a rather large application which uses this > syntax, because they were using informix. There is also a rather > interesting 4GL project called aubit which is on sourceforge. They would > also like to see this supported for the same reasons. > > Dave > > > On Wed, 2003-02-19 at 15:02, Patrick Welche wrote: > > > I have a large customer who is converting from informix to postgres and > > > they have made extensive use of > > > > > > update table set (col...) = ( val...) > > > > > > as a first pass would it be possible to translate this in the parser to > > > > > > update table set col=val > > > > > > It would appear that this is SQL3 compliant > > > > > > <set clause> ::= > > > <update target> <equals operator> <row value designator> > > > > > > <update target> ::= > > > <object column> > > > | <left paren> <object column list> <right paren> > > > > > > > > > or can someone think of another way? > > > > I don't understand the original problem. What does informix give you? A > > text file full of "update table set ()=()" which you then try to feed > > into postgres? In that case, why not pass said text file through a sed or > > perl script first? > > > > Cheers, > > > > Patrick > -- > Dave Cramer <dave@fastcrypt.com> > Cramer Consulting > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> Dave Cramer <dave@fastcrypt.com> writes: > > Ok, if a patch were submitted to the parser to allow the syntax in > > question would it be considered? > > I would vote against it ... but that's only one vote. I would vote against it too. Please don't add a none-standard syntax any more (I think we got enough lesson from "foo = NULL compatibilty" with MS products). -- Tatsuo Ishii
> While I don't see the syntax of: > > update table set (col...) = ( val...) > > as valuable compared to separate col=val assignments, I do see a value > in allowing subqueries in such assignments: > > update table set (col...) = ( select val ..) > > Without it, you have to do separate subquery statements, and if they are > complex, that is a waste. I assume that was the motivation for the > feature. The number of times I've needed this feature... :) Chris
Tom Lane wrote: > Dave Cramer <dave@fastcrypt.com> writes: > >>Ok, if a patch were submitted to the parser to allow the syntax in >>question would it be considered? > > > I would vote against it ... but that's only one vote. As a thought, will it add significant maintenance penalties or be detrimental? There seem to be quite a lot of Informix people moving to PostgreSQL these days, moreso than Oracle shops. Might have been brought on by IBM's purchase of Informix. Wondering if this one change be a significant improvement in regards to making it easier to migrate, or just a minor thing? Regards and best wishes, Justin Clift > regards, tom lane -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Justin, This is certainly the case here. I think IBM is deprecating informix, and many informix users are being forced to make a change, and they are seriously considering postgres as an alternative. It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before making this decision as well. I believe the aubit project has the potential to move postgres forward considerably as well. Dave On Wed, 2003-02-19 at 21:08, Justin Clift wrote: > Tom Lane wrote: > > Dave Cramer <dave@fastcrypt.com> writes: > > > >>Ok, if a patch were submitted to the parser to allow the syntax in > >>question would it be considered? > > > > > > I would vote against it ... but that's only one vote. > > As a thought, will it add significant maintenance penalties or be > detrimental? > > There seem to be quite a lot of Informix people moving to PostgreSQL > these days, moreso than Oracle shops. Might have been brought on by > IBM's purchase of Informix. > > Wondering if this one change be a significant improvement in regards to > making it easier to migrate, or just a minor thing? > > Regards and best wishes, > > Justin Clift > > > > regards, tom lane -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
On 19 Feb 2003, Dave Cramer wrote: > Justin, > > This is certainly the case here. I think IBM is deprecating informix, > and many informix users are being forced to make a change, and they are > seriously considering postgres as an alternative. Do you have any evidence that they are evaluating it? Gavin
After a long battle with technology,mike@aubit.com (Mike Aubury), an earthling, wrote: > On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote: >> I have a customer with a rather large application which uses this >> syntax, because they were using informix. There is also a rather >> interesting 4GL project called aubit which is on sourceforge. They would >> also like to see this supported for the same reasons. > > Hey - I was going to say that... > > For the curious: > Quick URL - http://aubit4gl.sourceforge.net/ > Its a 'clone' of the Informix 4GL tool, a nice 'clean' language > specifically designed for writing database applications, with both > curses & GTK, support for multiple database types and a bunch of > other things... > We're about to release version 0.30 - and I was going to wait until > then.... I tried it out a while back; couldn't get it to compile, probably due to there being a bit too much 'bleeding' to the 'bleeding edge.' It looks as though it could be pretty interesting, if PG support matures (which can certainly be a two way street!). How's the cross-platform support? Aubit would be an easier sell, to be sure, if it is readily deployable on Those Other Platforms, too... -- (concatenate 'string "cbbrowne" "@acm.org") http://www.ntlug.org/~cbbrowne/multiplexor.html "MSDOS didn't get as bad as it is overnight -- it took over ten years of careful development." -- <dmeggins@aix1.uottawa.ca>
Yes, the company in question is more than evaluating it; this request is a result of a project to port their application to postgres. Dave On Wed, 2003-02-19 at 22:32, Gavin Sherry wrote: > On 19 Feb 2003, Dave Cramer wrote: > > > Justin, > > > > This is certainly the case here. I think IBM is deprecating informix, > > and many informix users are being forced to make a change, and they are > > seriously considering postgres as an alternative. > > Do you have any evidence that they are evaluating it? > > Gavin -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Justin Clift <justin@postgresql.org> writes:
> As a thought, will it add significant maintenance penalties or be
> detrimental?
Well, yes it will if you look at the big picture. In the past we've
generally regretted it when we put in nonstandard features just to be
compatible with some other database. (Tatsuo already pointed out the
"foo = NULL" fiasco.) And we get ragged on regularly for the non-SQL-
standard features we've inherited from Berkeley Postgres (eg, the
implicit-FROM frammish that was under discussion yesterday).
I don't think we're really doing the users any favor either. If they
want to move to some other database after Postgres, are they likely to
get that other database to insert a not-very-useful nonstandard syntax?
Sooner or later they're going to have to bite this bullet, and it may
as well be sooner. (I can hardly believe that this is the worst
compatibility issue an ex-Informix user would face, anyhow.)
This is an Informix-ism. It should stay that way.
regards, tom lane
Agreed folks are going to have bigger problems from Informix than just this, and in fact I used Informix for years and didn't know they allowed this. However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. I think it makes sense to add it only if it adds functionality. --------------------------------------------------------------------------- Tom Lane wrote: > Justin Clift <justin@postgresql.org> writes: > > As a thought, will it add significant maintenance penalties or be > > detrimental? > > Well, yes it will if you look at the big picture. In the past we've > generally regretted it when we put in nonstandard features just to be > compatible with some other database. (Tatsuo already pointed out the > "foo = NULL" fiasco.) And we get ragged on regularly for the non-SQL- > standard features we've inherited from Berkeley Postgres (eg, the > implicit-FROM frammish that was under discussion yesterday). > > I don't think we're really doing the users any favor either. If they > want to move to some other database after Postgres, are they likely to > get that other database to insert a not-very-useful nonstandard syntax? > Sooner or later they're going to have to bite this bullet, and it may > as well be sooner. (I can hardly believe that this is the worst > compatibility issue an ex-Informix user would face, anyhow.) > > This is an Informix-ism. It should stay that way. > > 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 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> While I don't see the syntax of:
> update table set (col...) = ( val...)
> as valuable compared to separate col=val assignments, I do see a value
> in allowing subqueries in such assignments:
> update table set (col...) = ( select val ..)
Hm. That's at least got some defensibility to it. But does it do
anything that you can't already do with a join?
BTW, looking at the SQL99 standard, I see that you can do
UPDATE table SET ROW = foo WHERE ...
where foo is supposed to yield a row of the same rowtype as table
--- I didn't dig through the spec in detail, but I imagine foo can
be a sub-select. I don't care a whole lot for that, though, since it
would be a real pain in the neck if you're not updating all the columns.
You'd have to go
UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo)
which seems ugly, tedious, and error-prone.
regards, tom lane
On 19 Feb 2003, Dave Cramer wrote: > Yes, the company in question is more than evaluating it; this request is > a result of a project to port their application to postgres. Ahh. I thought you were referring to IBM. That is, that IBM was evaluating Postgres... Gavin
> BTW, looking at the SQL99 standard, I see that you can do > > UPDATE table SET ROW = foo WHERE ... > > where foo is supposed to yield a row of the same rowtype as table > --- I didn't dig through the spec in detail, but I imagine foo can > be a sub-select. I don't care a whole lot for that, though, since it > would be a real pain in the neck if you're not updating all the columns. > You'd have to go > > UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo) How is the Informix syntax any better? Chris
Christopher Kings-Lynne wrote: > > > BTW, looking at the SQL99 standard, I see that you can do > > > > UPDATE table SET ROW = foo WHERE ... > > > > where foo is supposed to yield a row of the same rowtype as table > > --- I didn't dig through the spec in detail, but I imagine foo can > > be a sub-select. I don't care a whole lot for that, though, since it > > would be a real pain in the neck if you're not updating all the columns. > > You'd have to go > > > > UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo) > > How is the Informix syntax any better? With Informix, you specify the columns you want updated in parens, rather than saying ROW. Does the spec allow a list of columns after ROW? That would be nice, like Informix. I doubt many folks update all the columns. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, Feb 19, 2003 at 12:29:12PM -0500, Tom Lane wrote: > SQL99. Looks like the parens got lost again by the time of the final > spec. I don't think the parens really matter. It's just the different ordering of columns and values. Michael -- Michael Meskes Email: Michael@Fam-Meskes.De ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Wed, Feb 19, 2003 at 04:37:33PM +0100, Peter Eisentraut wrote: > That's not what my copy says. Strange. I just looked at all the docs I have and all have it listed the way Dave wrote. So I seem to have to update my docs. Peter, could you send me a copy? Michael -- Michael Meskes Email: Michael@Fam-Meskes.De ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Tom Lane kirjutas K, 19.02.2003 kell 21:12: > Dave Cramer <dave@fastcrypt.com> writes: > > Ok, if a patch were submitted to the parser to allow the syntax in > > question would it be considered? > > I would vote against it ... but that's only one vote. Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? ------------- Hannu
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: > Agreed folks are going to have bigger problems from Informix than just > this, and in fact I used Informix for years and didn't know they allowed > this. > > However, what solution do we have for UPDATE (coll...) = (select val...) > for folks? It is awkward to repeat a query multiple times in an UPDATE. > > I think it makes sense to add it only if it adds functionality. It makes it easier (less keystrokes) to write as well as similar in appearance to INSERT, so the same code can be used to generate the queries. If we were at adding functionality then IMHO making VALUES(x,y,z) a proper "rowsource" would be a more worthy effort. --------------- Hannu
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: > Agreed folks are going to have bigger problems from Informix than just > this, and in fact I used Informix for years and didn't know they allowed > this. > > However, what solution do we have for UPDATE (coll...) = (select val...) > for folks? It is awkward to repeat a query multiple times in an UPDATE. hannu=# create table target (id serial, a int, b int, c int); NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for SERIAL column 'target.id' CREATE TABLE hannu=# insert into target(a,b,c) values (0,0,0); INSERT 16983 1 hannu=# insert into target(a,b,c) values (1,1,1); INSERT 16984 1 hannu=# update target set hannu-# a = source.a1, b=source.a2, c=source.a3 hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source hannu-# where id = 1 hannu-# ; UPDATE 1 hannu=# select * from target;id | a | b | c ----+---+---+--- 2 | 1 | 1 | 1 1 | 1 | 2 | 3 (2 rows) hannu=# -------------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> Are you against it just on grounds of cleanliness and ANSI compliance,
> or do you see more serious problems in letting it in ?
At this point it seems there are two different things being tossed
about. I originally understood Dave to be asking for parens to be
allowed around individual target column names, which seems a useless
frammish to me. What Bruce has pointed out is that a syntax that lets
you assign multiple columns from a single rowsource would be an actual
improvement in functionality, or at least in convenience and efficiency.
(It would also be a substantial bit of work, which is why I think this
isn't what Dave was offering a quick patch to do...) What I'd like to
know right now is which interpretation Informix actually implements.
I don't like adding nonstandard syntaxes that add no functionality ---
but if Informix has done what Bruce is talking about, that's a different
matter altogether.
regards, tom lane
Hannu Krosing <hannu@tm.ee> writes:
> Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
>> However, what solution do we have for UPDATE (coll...) = (select val...)
>> for folks? It is awkward to repeat a query multiple times in an UPDATE.
> hannu=# update target set
> hannu-# a = source.a1, b=source.a2, c=source.a3
> hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source
> hannu-# where id = 1
> hannu-# ;
I've been trying to think of a case that can't be handled by transposing
the sub-select into FROM. I'm not sure there are any. I thought for a
minute that grouped aggregates would be an issue. For example, suppose
table "totals" has one row for each distinct value of "groupid"
appearing in table "details", and you use it to store group aggregate
values. You can do
UPDATE totals SET xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), xmin = (SELECT min(x) FROM
detailsWHERE groupid = totals.groupid), ymax = (SELECT max(y) FROM details WHERE groupid = totals.groupid), ymin =
(SELECTmin(y) FROM details WHERE groupid = totals.groupid), ...
but that is awfully tedious and will be inefficiently implemented. This
is what Bruce is worried about. On the other hand, one could argue that
this is a wrongheaded way to go about it anyway, and the correct way is
UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM
detailsGROUP BY groupid) ss WHERE groupid = ss.groupid;
If there is indeed a row in "totals" for every groupid, then this will
certainly beat out the first approach that has to run a separate query
for each groupid, even if we avoid a separate query for each aggregate.
(It could maybe lose if you only wanted to update the totals for a few
groupids; but even then you could probably push the WHERE conditions
restricting the groups into the sub-select.)
Of course this syntax isn't standard either ... but we already have it.
Right now I'm not convinced there is a functionality argument for
supporting the Informix-style syntax, even with multiple columns.
regards, tom lane
On Thu, 20 Feb 2003, Tom Lane wrote: > Hannu Krosing <hannu@tm.ee> writes: > > Are you against it just on grounds of cleanliness and ANSI compliance, > > or do you see more serious problems in letting it in ? > > At this point it seems there are two different things being tossed > about. I originally understood Dave to be asking for parens to be > allowed around individual target column names, which seems a useless > frammish to me. What Bruce has pointed out is that a syntax that lets > you assign multiple columns from a single rowsource would be an actual > improvement in functionality, or at least in convenience and efficiency. > (It would also be a substantial bit of work, which is why I think this > isn't what Dave was offering a quick patch to do...) What I'd like to > know right now is which interpretation Informix actually implements. > > I don't like adding nonstandard syntaxes that add no functionality --- > but if Informix has done what Bruce is talking about, that's a different > matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.)
Scott, I can't find page 858 in that document, is it the right one? also the link s/b ? ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > On Thu, 20 Feb 2003, Tom Lane wrote: > > > Hannu Krosing <hannu@tm.ee> writes: > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > or do you see more serious problems in letting it in ? > > > > At this point it seems there are two different things being tossed > > about. I originally understood Dave to be asking for parens to be > > allowed around individual target column names, which seems a useless > > frammish to me. What Bruce has pointed out is that a syntax that lets > > you assign multiple columns from a single rowsource would be an actual > > improvement in functionality, or at least in convenience and efficiency. > > (It would also be a substantial bit of work, which is why I think this > > isn't what Dave was offering a quick patch to do...) What I'd like to > > know right now is which interpretation Informix actually implements. > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > but if Informix has done what Bruce is talking about, that's a different > > matter altogether. > > Tom, I was purusing the wild and wonderfully exciting new SQL > > (found here: > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > ANSI TC NCITS H2 > ISO/IEC JTC 1/SC 32/WG 3 > Database > > document to see what it had to say, and on this subject, and it looks like > update is going to be supporing this same style we're discussing here. > > Look on or around p. 858 in that doc.) -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
Scott, Thanks for the reference, I think the actual document is ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf and it is in section 14.12 on or about page 839 Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > On Thu, 20 Feb 2003, Tom Lane wrote: > > > Hannu Krosing <hannu@tm.ee> writes: > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > or do you see more serious problems in letting it in ? > > > > At this point it seems there are two different things being tossed > > about. I originally understood Dave to be asking for parens to be > > allowed around individual target column names, which seems a useless > > frammish to me. What Bruce has pointed out is that a syntax that lets > > you assign multiple columns from a single rowsource would be an actual > > improvement in functionality, or at least in convenience and efficiency. > > (It would also be a substantial bit of work, which is why I think this > > isn't what Dave was offering a quick patch to do...) What I'd like to > > know right now is which interpretation Informix actually implements. > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > but if Informix has done what Bruce is talking about, that's a different > > matter altogether. > > Tom, I was purusing the wild and wonderfully exciting new SQL > > (found here: > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > ANSI TC NCITS H2 > ISO/IEC JTC 1/SC 32/WG 3 > Database > > document to see what it had to say, and on this subject, and it looks like > update is going to be supporing this same style we're discussing here. > > Look on or around p. 858 in that doc.) -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
sorry, it's the -02 document. just change the last 01 to 02 and you'll get the right one. On 20 Feb 2003, Dave Cramer wrote: > Scott, > > I can't find page 858 in that document, is it the right one? > > also the link s/b ? > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <hannu@tm.ee> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) >
On Thu, Feb 20, 2003 at 09:31:21AM -0500, Tom Lane wrote: > about. I originally understood Dave to be asking for parens to be > allowed around individual target column names, which seems a useless > frammish to me. What Bruce has pointed out is that a syntax that lets > you assign multiple columns from a single rowsource would be an actual > improvement in functionality, or at least in convenience and efficiency. > (It would also be a substantial bit of work, which is why I think this > isn't what Dave was offering a quick patch to do...) What I'd like to > know right now is which interpretation Informix actually implements. Informix syntax is listed on http://www-3.ibm.com/software/data/informix/pubs/library/visionary/infoshelf/sqls/01start.fm.html#156200 It's more than just parens IMO. :-) Michael -- Michael Meskes Email: Michael@Fam-Meskes.De ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
The right URL (I'll get it eventually) is ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf That time I exactly copied the URL. sorry for the wrong one previously. On 20 Feb 2003, Dave Cramer wrote: > Scott, > > Thanks for the reference, I think the actual document is > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > and it is in section 14.12 > > > on or about page 839 > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <hannu@tm.ee> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) >
Tom Lane wrote: > UPDATE totals SET > xmax = ss.xmax, xmin = ss.xmin, ... > FROM > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss > WHERE groupid = ss.groupid; As long as any individual item that you can express in the parenthesized (Informix) syntax can also be expressed as an element in a SELECT, then the above is equivalent in every way to the Informix syntax. And since SELECT allows subselects, it seems to me that the PG syntax is complete. My question is whether or not there's likely to be an approved standard way of accomplishing what either syntax does. Is there anything in the current draft that addresses this? -- Kevin Brown kevin@sysexperts.com
Tom Lane <tgl@sss.pgh.pa.us> writes: > UPDATE totals SET > xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), > ... > > but that is awfully tedious and will be inefficiently implemented. This > is what Bruce is worried about. On the other hand, one could argue that > this is a wrongheaded way to go about it anyway, and the correct way is > > UPDATE totals SET > xmax = ss.xmax, xmin = ss.xmin, ... > FROM > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss > WHERE groupid = ss.groupid; ... > Of course this syntax isn't standard either ... but we already have it. This is nice, but I could see it being a big pain if the join clause wasn't so neat and tidy as a groupid column that you can group by. The Informix syntax has some appeal -- speaking from the point of view of someone who has had to write some awkward update statements like this in the past. (In Oracle where the best syntax is to create an updatable inline view which is pretty much equivalent in expressiveness to the Postgres syntax.) Consider how awkward this query would be if the iterations in the original query overlapped for example. You would have to introduce a another table to the select just to drive the join artificially. For example consider a hypothetical case: UPDATE networks set num_hosts = (select count(*) from hosts where addr << netblock) Where some hosts are on multiple nested netblocks. The only way I see to convert that to Postgres's syntax would be to join against the networks table again and then group by the primary key of the networks table. Ick. -- greg
On Thu, 20 Feb 2003, Kevin Brown wrote: > Tom Lane wrote: > > UPDATE totals SET > > xmax = ss.xmax, xmin = ss.xmin, ... > > FROM > > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss > > WHERE groupid = ss.groupid; > > As long as any individual item that you can express in the > parenthesized (Informix) syntax can also be expressed as an element in > a SELECT, then the above is equivalent in every way to the Informix > syntax. And since SELECT allows subselects, it seems to me that the > PG syntax is complete. > > My question is whether or not there's likely to be an approved > standard way of accomplishing what either syntax does. Is there > anything in the current draft that addresses this? Yes there is. I've posted the URL on the hackers list a while back, but here it is again: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf pp 851 to 862, in particular, p 858 defines the the <set clause list> as supporting <multiple column assignment> as supporting something like: (target1, target2, target3) = (value1, value2, value3)
Given that the direction of the spec seems to be headed towards the desired syntax, can we put this on the TODO list? Dave On Thu, 2003-02-20 at 11:49, Dave Cramer wrote: > Scott, > > I can't find page 858 in that document, is it the right one? > > also the link s/b ? > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <hannu@tm.ee> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) -- Dave Cramer <dave@fastcrypt.com> Cramer Consulting
While I can see a subquery in UPDATE as working in most cases:
UPDATE tabSET col - t.colFROM (SELECT col from xx) AS tWHERE ...
but I don't see that working for correlated subqueries, where you want
to set a column based on a value you are updating. (Many use correlated
subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
subqueries? I can't see how they would because you don't have a row
being processed at the FROM stage of the query.
I did look at the SQL99 standards and ROW does appear there:
<update statement: positioned> ::= UPDATE <target table> SET <set clause list>
WHERE CURRENT OF <cursor name>
<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<set clause> ::= <update target> <equals operator> <update source> | <mutated set
clause><equals operator> <update source>
<update target> ::= <object column>
--> | ROW | <object column> <left bracket or trigraph> <simple value
specification><right bracket or trigraph>
and later it says:
a) If <update target> specifies ROW, then let CL be the set of all columns of T.
The TODO item would be:
Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
This also gets into that weird Informix syntax where you have to
double-paren when you want to use a subquery. Basically, this thing
keeps getting wierder and wierder.
---------------------------------------------------------------------------
Dave Cramer wrote:
> Given that the direction of the spec seems to be headed towards the
> desired syntax, can we put this on the TODO list?
>
> Dave
>
> On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> > Scott,
> >
> > I can't find page 858 in that document, is it the right one?
> >
> > also the link s/b ?
> >
> > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
> >
> > Dave
> > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > > On Thu, 20 Feb 2003, Tom Lane wrote:
> > >
> > > > Hannu Krosing <hannu@tm.ee> writes:
> > > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > > or do you see more serious problems in letting it in ?
> > > >
> > > > At this point it seems there are two different things being tossed
> > > > about. I originally understood Dave to be asking for parens to be
> > > > allowed around individual target column names, which seems a useless
> > > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > > you assign multiple columns from a single rowsource would be an actual
> > > > improvement in functionality, or at least in convenience and efficiency.
> > > > (It would also be a substantial bit of work, which is why I think this
> > > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > > know right now is which interpretation Informix actually implements.
> > > >
> > > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > > but if Informix has done what Bruce is talking about, that's a different
> > > > matter altogether.
> > >
> > > Tom, I was purusing the wild and wonderfully exciting new SQL
> > >
> > > (found here:
> > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> > >
> > > ANSI TC NCITS H2
> > > ISO/IEC JTC 1/SC 32/WG 3
> > > Database
> > >
> > > document to see what it had to say, and on this subject, and it looks like
> > > update is going to be supporing this same style we're discussing here.
> > >
> > > Look on or around p. 858 in that doc.)
> --
> Dave Cramer <dave@fastcrypt.com>
> Cramer Consulting
>
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Dave, Justin, I have several Informix clients who will be moving to a Postgresql/Aubit4gl solution at some point. The Informix line is, for them, a dead end. One way or another the backend will become Postgresql. Because of the number of SQL statements, I would encourage support where possible and reasonable. Jordan ----- Original Message ----- From: "Dave Cramer" <dave@fastcrypt.com> To: "Justin Clift" <justin@postgresql.org> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Peter Eisentraut" <peter_e@gmx.net>; "Pgsql Hackers" <pgsql-hackers@postgresql.org> Sent: Wednesday, February 19, 2003 10:18 PM Subject: Re: [HACKERS] request for sql3 compliance for the update command > Justin, > > This is certainly the case here. I think IBM is deprecating informix, > and many informix users are being forced to make a change, and they are > seriously considering postgres as an alternative. > > It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before > making this decision as well. > > > I believe the aubit project has the potential to move postgres forward > considerably as well. > > Dave > > On Wed, 2003-02-19 at 21:08, Justin Clift wrote: > > Tom Lane wrote: > > > Dave Cramer <dave@fastcrypt.com> writes: > > > > > >>Ok, if a patch were submitted to the parser to allow the syntax in > > >>question would it be considered? > > > > > > > > > I would vote against it ... but that's only one vote. > > > > As a thought, will it add significant maintenance penalties or be > > detrimental? > > > > There seem to be quite a lot of Informix people moving to PostgreSQL > > these days, moreso than Oracle shops. Might have been brought on by > > IBM's purchase of Informix. > > > > Wondering if this one change be a significant improvement in regards to > > making it easier to migrate, or just a minor thing? > > > > Regards and best wishes, > > > > Justin Clift > > > > > > > regards, tom lane > -- > Dave Cramer <dave@fastcrypt.com> > Cramer Consulting > > > ---------------------------(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
With no one replying on how to do correlated subqueries in FROM for
UPDATE, I am adding this to the TODO list:
* Allow UPDATE tab SET ROW (col, ...) = (...) for updating multiple columns
Several people indicated they wanted this functionality. The ROW is SQL
standard, and the column list is an extension.
I do not see any way to allow subqueries without requiring two levels of
parentheses, one for the list, another for the subquery. ROW should
also be optional.
---------------------------------------------------------------------------
Bruce Momjian wrote:
>
> While I can see a subquery in UPDATE as working in most cases:
>
> UPDATE tab
> SET col - t.col
> FROM (SELECT col from xx) AS t
> WHERE ...
>
> but I don't see that working for correlated subqueries, where you want
> to set a column based on a value you are updating. (Many use correlated
> subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
> subqueries? I can't see how they would because you don't have a row
> being processed at the FROM stage of the query.
>
> I did look at the SQL99 standards and ROW does appear there:
>
> <update statement: positioned> ::=
> UPDATE <target table>
> SET <set clause list>
> WHERE CURRENT OF <cursor name>
>
> <set clause list> ::=
> <set clause> [ { <comma> <set clause> }... ]
>
> <set clause> ::=
> <update target> <equals operator> <update source>
> | <mutated set clause> <equals operator> <update source>
>
> <update target> ::=
> <object column>
> --> | ROW
> | <object column>
> <left bracket or trigraph> <simple value specification> <right bracket or trigraph>
>
> and later it says:
>
> a) If <update target> specifies ROW, then let CL be the set of
> all columns of T.
>
> The TODO item would be:
>
> Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
>
> This also gets into that weird Informix syntax where you have to
> double-paren when you want to use a subquery. Basically, this thing
> keeps getting wierder and wierder.
>
> ---------------------------------------------------------------------------
>
> Dave Cramer wrote:
> > Given that the direction of the spec seems to be headed towards the
> > desired syntax, can we put this on the TODO list?
> >
> > Dave
> >
> > On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> > > Scott,
> > >
> > > I can't find page 858 in that document, is it the right one?
> > >
> > > also the link s/b ?
> > >
> > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
> > >
> > > Dave
> > > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > > > On Thu, 20 Feb 2003, Tom Lane wrote:
> > > >
> > > > > Hannu Krosing <hannu@tm.ee> writes:
> > > > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > > > or do you see more serious problems in letting it in ?
> > > > >
> > > > > At this point it seems there are two different things being tossed
> > > > > about. I originally understood Dave to be asking for parens to be
> > > > > allowed around individual target column names, which seems a useless
> > > > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > > > you assign multiple columns from a single rowsource would be an actual
> > > > > improvement in functionality, or at least in convenience and efficiency.
> > > > > (It would also be a substantial bit of work, which is why I think this
> > > > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > > > know right now is which interpretation Informix actually implements.
> > > > >
> > > > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > > > but if Informix has done what Bruce is talking about, that's a different
> > > > > matter altogether.
> > > >
> > > > Tom, I was purusing the wild and wonderfully exciting new SQL
> > > >
> > > > (found here:
> > > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> > > >
> > > > ANSI TC NCITS H2
> > > > ISO/IEC JTC 1/SC 32/WG 3
> > > > Database
> > > >
> > > > document to see what it had to say, and on this subject, and it looks like
> > > > update is going to be supporing this same style we're discussing here.
> > > >
> > > > Look on or around p. 858 in that doc.)
> > --
> > Dave Cramer <dave@fastcrypt.com>
> > Cramer Consulting
> >
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(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
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian kirjutas E, 17.03.2003 kell 20:49: > With no one replying on how to do correlated subqueries in FROM for > UPDATE, Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug, so the way to do correlated subqueries in FROM for UPDATE would be to fix this bug ;) All common sense tells me that if I can update set col1=col2 and *not* get the value from the first col2 to all col1's then the same should be true for this hannu=# creatre table updtarget( hannu(# id int, val text); ERROR: parser: parse error at or near "creatre" at character 1 hannu=# create table updtarget(id int, val text); CREATE TABLE hannu=# create table updsource(id int, val text); CREATE TABLE hannu=# insert into updtarget(id) values (1); INSERT 16995 1 hannu=# insert into updtarget(id) values (2); INSERT 16996 1 hannu=# insert into updsource(id,val) values (1,'one'); INSERT 16997 1 hannu=# insert into updsource(id,val) values (2,'two'); INSERT 16998 1 hannu=# update updtarget set val = src.val hannu-# from (select s.val from updsource s hannu-# where s.id=updtarget.id) as src hannu-# ; NOTICE: Adding missing FROM-clause entry in subquery for table "updtarget" UPDATE 2 hannu=# select * from updtarget;id | val ----+----- 1 | one 2 | one (2 rows) there should be no need to add "missing FROM-clause entry" and the result *should* be: hannu=# select * from updtarget;id | val ----+----- 1 | one 2 | two (2 rows) -------------------- Hannu
I wasn't sure it made logical sense to allow correlated subqueries in FROM because the FROM is processed before the WHERE. --------------------------------------------------------------------------- Hannu Krosing wrote: > Bruce Momjian kirjutas E, 17.03.2003 kell 20:49: > > With no one replying on how to do correlated subqueries in FROM for > > UPDATE, > > Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug, > so the way to do correlated subqueries in FROM for UPDATE would be to > fix this bug ;) > > All common sense tells me that if I can update set col1=col2 and *not* > get the value from the first col2 to all col1's then the same should be > true for this > > hannu=# creatre table updtarget( > hannu(# id int, val text); > ERROR: parser: parse error at or near "creatre" at character 1 > hannu=# create table updtarget(id int, val text); > CREATE TABLE > hannu=# create table updsource(id int, val text); > CREATE TABLE > hannu=# insert into updtarget(id) values (1); > INSERT 16995 1 > hannu=# insert into updtarget(id) values (2); > INSERT 16996 1 > hannu=# insert into updsource(id,val) values (1,'one'); > INSERT 16997 1 > hannu=# insert into updsource(id,val) values (2,'two'); > INSERT 16998 1 > hannu=# update updtarget set val = src.val > hannu-# from (select s.val from updsource s > hannu-# where s.id=updtarget.id) as src > hannu-# ; > NOTICE: Adding missing FROM-clause entry in subquery for table > "updtarget" > UPDATE 2 > hannu=# select * from updtarget; > id | val > ----+----- > 1 | one > 2 | one > (2 rows) > > there should be no need to add "missing FROM-clause entry" and the > result *should* be: > > hannu=# select * from updtarget; > id | val > ----+----- > 1 | one > 2 | two > (2 rows) > > -------------------- > Hannu > > > > ---------------------------(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 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I wasn't sure it made logical sense to allow correlated subqueries in
> FROM because the FROM is processed before the WHERE.
It doesn't; in fact it violates the whole semantic model of SQL,
as far as I can see. Sub-selects in FROM are (in principle)
evaluated separately and then joined. They can't have cross-references.
I think there is some weird construct in SQL99 that alters this behavior,
though.
regards, tom lane
Tom Lane kirjutas K, 19.03.2003 kell 16:46: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I wasn't sure it made logical sense to allow correlated subqueries in > > FROM because the FROM is processed before the WHERE. > > It doesn't; in fact it violates the whole semantic model of SQL, > as far as I can see. Sub-selects in FROM are (in principle) > evaluated separately and then joined. They can't have cross-references. Makes sense. What I was describing would have been akin to updatable queries where you first do all the joining and then update one of the underlying tables. the more accurate (nonstandard) syntax could have been SELECT src.val, tgt.val FROM updatesrc as src FOR UPDATE, updatetgd as tgtWHERE src.id = tgt.id SET src.val =tgt.val ; > I think there is some weird construct in SQL99 that alters this behavior, > though. You probably mean WITH, which acts like FROM but has lexically previous (or all in case of WITH RECURSIVE) sub-selects in its namespace. ---------------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> the more accurate (nonstandard) syntax could have been
>
> SELECT src.val,
> tgt.val
> FROM updatesrc as src FOR UPDATE,
> updatetgd as tgt
> WHERE src.id = tgt.id
> SET src.val = tgt.val
> ;
The syntax in Oracle, for example, would be not very different:
UPDATE ( SELECT src.id, src.val, tgt.val as newval FROM udpatesrc AS src, updatetgd AS tgt
WHERE src.id = tgt.id ) SET val = newval
This only works if src.id is declared as a primary key.
I'm not sure if this is blessed by any standard.
It's certainly extremely useful.
--
greg
Tom Lane <tgl@sss.pgh.pa.us> writes: > UPDATE totals SET > xmax = ss.xmax, xmin = ss.xmin, ... > FROM > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss > WHERE groupid = ss.groupid; > ... > > Of course this syntax isn't standard either ... but we already have it. Did this patch ever make it in? It's not documented in the 8.0 documentation for UPDATE at: http://www.postgresql.org/docs/8.0/interactive/dml-update.html -- greg
Greg Stark wrote: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > UPDATE totals SET > > xmax = ss.xmax, xmin = ss.xmin, ... > > FROM > > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss > > WHERE groupid = ss.groupid; > > > ... > > > > Of course this syntax isn't standard either ... but we already have it. > > > Did this patch ever make it in? It's not documented in the 8.0 documentation > for UPDATE at: > > http://www.postgresql.org/docs/8.0/interactive/dml-update.html It is documented only in the UPDATE manual page because it is fairly exotic: http://www.postgresql.org/docs/8.0/interactive/sql-update.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073