Batch insert into two tables with array unnesting

Question

I've seen a few posts about inserting into two tables with one query using CTEs. See Insert data in 3 tables at a time using Postgres

Is there a way to do so for data that contains an array to be unnested?

Example:

CREATE TABLE parent (
   id        serial PRIMARY KEY,
   name      text
);

CREATE TABLE child(
   id        serial PRIMARY KEY,
   name      text,
   parent_id integer references parent
);

I'd like to insert my data, which is of the form

(parent_name, children_names) as (values (
('Parent1', ARRAY['Parent1-Child1', 'Parent1-Child2']),
('Parent2', ARRAY['Parent2-Child1']),
...,
))

For additional context> I insert one "superparent" at a time, which consists of multiple parents, so I'd like be able to insert multiple parents at the same time, as opposed to one parent at a time.

score 0 · Answer 1 · answered Sep 09 '21 at 20:05

with sample (name, children) as (values
  ('Parent1', '{"Parent1-Child1", "Parent1-Child2"}'),
  ('Parent2', '{"Parent2-Child1"}')
),
parent_insert as (
  insert into parent(name)
  select name from sample
  returning id 
),
children_data as (
  select
    child_name as name,
    ordinality
  from
    sample, unnest(children::text[]) with ordinality child_name
),
parent_data as (
  select
    id - min_id + 1 as id
  from
  parent_insert, (select min(id) as min_id from parent_insert ) t
)
insert into child (parent_id, name)
select
  id as id,
  name
from
  parent_data
join
  children_data
on
  ordinality = id

This doesn't seem ideal but I think it works.

Batch insert into two tables with array unnesting

1 Answers1