Insert statement, returning columns from source table

user_11706662 0
Viewed 362

For a bulk insert, I have a foreign reference value. As part of the insert, I'd like to return both the reference and corresponding id's of the newly created records in order to create a mapping record in another system.

Using "RETURNING" works fine for the target table. Other than creating a dummy column in the target table, is there anyway to achieve what I'm trying to do?

Definitely do not want to do row-by-row processing.

NOTE: Currently using version 10.7

In my sample code, I tried "returning id, source.ref", but obviously this isn't supported.

create table test( id serial primary key, name varchar);

insert into test( name)
select source.name
from ( values('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c') ) source(ref, name)
returning id --, source.ref
sql-returning postgresql
2 Answers

Use CTEs:

WITH a AS (
   INSERT ...
   RETURNING ...
), b AS (
   INSERT ...
   RETURNING ...
)
SELECT ...
FROM a JOIN b ON ...
user_64643080

Reference back to the source, if it is unique. Try something like this:

WITH q AS ( INSERT  INTO test (name)
              SELECT source.name
              FROM ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
                   ) AS source (ref, name)
            RETURNING * )
SELECT q.id, source.ref
FROM q
  JOIN ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
       ) AS source (ref, name) ON q.name = source.name

But if you want to add this mapping to another table, you might consider to re-structure your queries, to something like this:

INSERT INTO mapping ( ref, id )
  SELECT source.ref, ( INSERT INTO test (name) VALUES ( source.name ) RETURNING id )
  FROM ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
                 ) AS source (ref, name) )
user_48991930
Related