From: | Aleksandr Furmanov <aleksandr(dot)furmanov(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | INSERT RETURNING with values other than inserted ones. |
Date: | 2013-06-10 23:29:39 |
Message-ID: | C47F63F6-B959-4A22-9F37-7A43950EB2EA@nationbuilder.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I want to insert new values into target table 'a' from source table 'b', and then update table 'b' with ids from table 'a', somewhat like:
CREATE TABLE a(id SERIAL, name TEXT);
INSERT INTO a (name) VALUES('Jason');
INSERT INTO a (name) VALUES('Peter');
CREATE TABLE b(row_id serial, id INT, name TEXT);
INSERT INTO b (name) VALUES('Jason');
INSERT INTO b (name) VALUES('Peter');
WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name = name RETURNING a.id)
UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;
However this would not work for obvious reason:
WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot return row_id.
What can be returned are only columns of 'a', but they are insufficient to identify matching records of 'b'.
So the question is - what to put in WHERE clause to match RETURNING with rows being inserted from 'b'?
Thanks!
--
Aleksandr Furmanov
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Dunks | 2013-06-10 23:39:20 | Re: INSERT RETURNING with values other than inserted ones. |
Previous Message | John R Pierce | 2013-06-10 22:42:59 | Re: databse version |