Thread: INSERT RETURNING with values other than inserted ones.

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

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
IDsuffice? 

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
needto 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@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks,
However I am not just replicating data from 'a' to 'b', I provided simplified example, in reality table 'b' keeps data
whichare 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
AID 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
youneed 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@gmail.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

Aleksandr Furmanov wrote
> 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.

Three options:

1) CREATE TABLE a (id serial, name text, source_id_from_table_b integer);
and during INSERT provide which record on B caused the record on A to be
created.

2) Determine what can be used as a true primary key on both A and B; forgo
the use of SERIAL

3) Do whatever it is you need to do one record at a time within a pl/pgsql
function and capture the ID from B and the post-insert ID from A into local
variables to generate a temporary mapping for doing your other work.

I'd suggest #1; simply move the mapping column from "B" to "A" and perform
the linking during the insert but your simplified example provide no context
to evaluate if this will actually meet your needs.  The main concern is that
this basically models a 1-to-1(optional) relationship between A and B; such
that every record in A must exist in B (assuming you disallow NULL) but B
can have records that do not exist in A.  Whether multiple records in A can
share the same "source" record in B is undefined by can be made explicit by
the choice of INDEX that you create (UNIQUE or NORMAL).

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/INSERT-RETURNING-with-values-other-than-inserted-ones-tp5758695p5758793.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.