Insert statement, returning columns from source table

Question

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

Rafflesia

Answer 1

Use CTEs:

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

Fob

Could you please clarify how to write the original query in the question using CTEs? I'm just not seeing it, although I've not had my morning coffee so... :) – Flan

Ok, there is guesswork involved. OP says he has a foreign key reference, so I guessed he wants to insert a row in both tables and return the generated primary keys. A long shot, I admit. – Fob

Hi Laurenz, the data is from a separate system and there is only 1 target table. The source table has my foreign keys, and am hoping to get some mapping information as part of the insert. I'm new to postgres, I usually work with SQL Server and could normally achieve this with simple merge statement. – Rafflesia

You can replace the first INSERT with a SELECT or something else that returns the foreign keys. – Fob

Answer 2

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) )

Wahlstrom

This gets me close to the answer, it's not ideal because of the requirement of unique names. – Rafflesia