Thanks,
However I am not just replicating data from 'a' to 'b', I provided simplified example, in reality table 'b' keeps data which are going to be merged into 'a', some rows will be updated, some added. There is some other work has to be done on 'b' before merging into 'a' and that work relies on 'id' from a.
On Jun 10, 2013, at 4:39 PM, Richard Dunks wrote:
> 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.furmanov@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
