Postgres bulk insert then insert association rows with returned ids & other data

I'm trying to discover if there is a way to do a bulk insert returning the created row IDs while also matching those up to values that do not get inserted in Postgres.

I have a Person table, a Person_Action table, and an Action table.

I am importing data with a Person's ID and an Action that they do. I need to create the new Action row, return the ID, and create a Person_Action association row with the new Action ID and the Person's ID.

I was hoping I could do something like this (in a less pseudo-code fashion):

Actions = [
    {
        name: 'made soup'
        person: 1
    },
    {
        name: 'ate soup'
        person: 2
    }
]

INSERT INTO Action
    VALUES UNNEST(Array[actions.name])
    WITH UNNEST(Array[actions.person])
    RETURNING Action.id as action_id, person as person_id

INSERT INTO Person_Action
    VALUES (UNNEST(Array[action_id), UNNEST(Array[person_id])

I have spent some time researching this (including seeing if I could simply match the person_id to the returned action_id in sequence, but it looks like the returned sequence can vary from the inserted sequence, so that would not work).

I imagine there's got to be a way to do something like this, maybe with a completely different technique.

Now that I think about it, this may be a great example of the XY problem. Really, what I'm trying to achieve is this: How would I go about inserting Actions for persons, then create the association rows in person_action for them (in bulk inserts) without creating a redundant person_id column on the Action table?

Solution

You can insert into any/all of the 3 tables at once by cramming insert..returning into common table expressions. See the demo:

Spawn all 3 at once, already linked:

with 
 new_action as 
(   insert into action values (default,default) returning *)
,new_person as 
(   insert into person values (default,default) returning *)
insert into person_action (fk_person_id,fk_action_id) 
select p.id as fk_person_id,
       a.id as fk_action_id
from new_person as p, 
     new_action as a;

Find a person, link a new action to the person:

with 
 select_person(id) as 
(   select id from person where name like '%person%b%' limit 1)
,new_action as 
(   insert into action values (default,default) returning *)
insert into person_action (fk_person_id,fk_action_id) 
select p.id as fk_person_id,
       a.id as fk_action_id
from select_person as p, 
     new_action as a;

Link a new action to a previously known person:

with 
 known_person(id) as 
(   values (2) )
,new_action as 
(   insert into action values (default,default) returning *)
insert into person_action (fk_person_id,fk_action_id) 
select p.id as fk_person_id,
       a.id as fk_action_id
from known_person as p, 
     new_action as a;