Re: INSERT RETURNING with values other than inserted ones.

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.