From: Craig Ringer <craig@postnewspapers.com.au>
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
Cc: Gurjeet Singh <singh.gurjeet@gmail.com>
Cc: pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Subject: Re: Select into
Date: Thu, 20 Mar 2008 21:05:12 +0900
Message-ID: <47E252F8.6030008@postnewspapers.com.au> (raw)
In-Reply-To: <786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au>
References: <94F93C0F-DA95-4435-B253-57C90961DF34@eclinic.com.au>
<20080320104922.GB18935@a-kretschmer.de>
<65937bea0803200358i290bdc87hdeca777b593b5df1@mail.gmail.com>
<786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au>
Gavin 'Beau' Baumanis wrote:
>
> The copy is inside the same table, so I don't understand why it (the
> required query ) would require any joins.
Maybe you should use FROM clause in the update that references a
row-valued subquery?
craig=# create table x ( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "x_id_seq" for
serial column "x.id"
CREATE TABLE
craig=# insert into x ( val ) values ( 4 ) , ( 6 ) ;
INSERT 0 2
craig=# select * from x;
id | val
----+-----
1 | 4
2 | 6
(2 rows)
craig=# update x set val = foundrow.val from ( select val from x where
id = 2 ) as foundrow where id = 1 ;
UPDATE 1
craig=# select * from x;
id | val
----+-----
2 | 6
1 | 6
(2 rows)
craig=# insert into x ( val ) select generate_series(0,10000);
INSERT 0 10001
craig=# explain update x set val = foundrow.val from ( select val from x
where id = 4123 ) as foundrow where id = 5912 ;
QUERY PLAN
-------------------------------------------------------------------------
Nested Loop (cost=0.00..16.55 rows=1 width=14)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10)
Index Cond: (id = 5912)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=4)
Index Cond: (public.x.id = 4123)
(5 rows)
Will that do the job?
--
Craig Ringer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, gavinb@eclinic.com.au, singh.gurjeet@gmail.com, andreas.kretschmer@schollglas.com
Subject: Re: Select into
In-Reply-To: <47E252F8.6030008@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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