Re: INSERT RETURNING with values other than inserted ones.

If you're just replicating the data from table A into table B, why does it need its own ID number? Wouldn't the table A ID suffice?

I'd recommend using the following:

CREATE TABLE b AS ( SELECT * FROM a );

This way, you only define the columns and insert the data once, then let Postgres do the rest for you. Obviously if you need to have a separate table B ID, you can alter as necessary.

Good luck,
Richard Dunks

On Jun 10, 2013, at 7:29 PM, Aleksandr Furmanov <aleksandr(dot)furmanov(at)gmail(dot)com> wrote:

> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general