pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUse select and update together
6+ messages / 6 participants
[nested] [flat]
* Use select and update together
@ 2011-09-12 10:36 Boris <kuzikoff@ukr.net>
0 siblings, 2 replies; 6+ messages in thread
From: Boris @ 2011-09-12 10:36 UTC (permalink / raw)
To: pgsql-sql
Hi, this is my first on this list.
I can't understand where my problem in PG 8.4.
CREATE TABLE tbl( KEY int, val int);
Update query like:
UPDATE tbl SET val = 1
WHERE KEY = any('{0,1,2,3,4,5}'::int[])
returning key;
work well. But any try to wrap it in select query like:
SELECT (
UPDATE tbl SET val = 1
WHERE KEY = any('{0,1,2,3,4,5}'::int[])
returning key
);
cause syntax error. Is any query of such type (update warped into
select) is possible?
Best regards, Kuzikov Borys
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use select and update together
@ 2011-09-13 05:50 =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
parent: Boris <kuzikoff@ukr.net>
1 sibling, 1 reply; 6+ messages in thread
From: pasman pasmański @ 2011-09-13 05:50 UTC (permalink / raw)
To: pgsql-sql
In 8.4 this syntax is not implemented.
2011/9/12, Boris <kuzikoff@ukr.net>:
> Hi, this is my first on this list.
>
> I can't understand where my problem in PG 8.4.
>
> CREATE TABLE tbl( KEY int, val int);
>
> Update query like:
>
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key;
>
> work well. But any try to wrap it in select query like:
>
> SELECT (
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
> Best regards, Kuzikov Borys
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
------------
pasman
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use select and update together
@ 2011-09-13 06:45 Samuel Gendler <sgendler@ideasculptor.com>
parent: Boris <kuzikoff@ukr.net>
1 sibling, 0 replies; 6+ messages in thread
From: Samuel Gendler @ 2011-09-13 06:45 UTC (permalink / raw)
To: Boris <kuzikoff@ukr.net>; +Cc: pgsql-sql
On Mon, Sep 12, 2011 at 3:36 AM, Boris <kuzikoff@ukr.net> wrote:
>
> SELECT (
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
Just lose the select (); part.
update tbl set val = 1 where condition = true returning *;
That works just fine on 8.4. You can also specify individual columns in the
returning clause. The postgresql documentation covers the topic.
http://www.postgresql.org/docs/9.0/static/sql-update.html
It doesn't appear to work in a subquery, so you can't join to it (though you
can use a join within the update clause and then return the columns you need
from a joined table, I suspect). Simply returning 1 or more columns from
updated rows works just fine, though.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use select and update together
@ 2011-09-17 14:56 Andreas <maps.on@gmx.net>
parent: =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas @ 2011-09-17 14:56 UTC (permalink / raw)
To: pgsql-sql
Am 13.09.2011 07:50, schrieb pasman pasmański:
> In 8.4 this syntax is not implemented.
select * from (
update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *
) as x
wouldn't work even in PG 9.1.
So what data structure is coming out of an "update ... returning *"
statement?
It obviously doesn't work like a subquery.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use select and update together
@ 2011-09-17 15:51 Guillaume Lelarge <guillaume@lelarge.info>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2011-09-17 15:51 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote:
> Am 13.09.2011 07:50, schrieb pasman pasmański:
> > In 8.4 this syntax is not implemented.
>
> select * from (
> update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *
> ) as x
>
> wouldn't work even in PG 9.1.
> So what data structure is coming out of an "update ... returning *"
> statement?
> It obviously doesn't work like a subquery.
>
The only way to make something like this work in 9.1 would be:
WITH x AS
(update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *)
SELECT * FROM x;
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Use select and update together
@ 2011-09-17 16:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2011-09-17 16:16 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Andreas <maps.on@gmx.net>; pgsql-sql
Guillaume Lelarge <guillaume@lelarge.info> writes:
> On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote:
>> select * from (
>> update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *
>> ) as x
>>
>> wouldn't work even in PG 9.1.
>> So what data structure is coming out of an "update ... returning *"
>> statement?
>> It obviously doesn't work like a subquery.
> The only way to make something like this work in 9.1 would be:
> WITH x AS
> (update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *)
> SELECT * FROM x;
The reason for that restriction is that WITH guarantees that the
contained query is evaluated once and only once, whereas an ordinary
subquery guarantees no such thing. So the effects of the UPDATE would
be quite unpredictable if we allowed the former syntax. (In the
specific example given it would likely work all right anyway, since
there is no reason for a plain SELECT FROM to do anything except scan
the subquery once. But if you did a join, say, watch out!)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-09-17 16:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-12 10:36 Use select and update together Boris <kuzikoff@ukr.net>
2011-09-13 05:50 ` =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
2011-09-17 14:56 ` Andreas <maps.on@gmx.net>
2011-09-17 15:51 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-09-17 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-13 06:45 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox