When doing bulk inserts is it possible to insert into two separate tables and add the relationship? So I have something like this:
create table collector (
id int4 generated by default as identity primary key,
one int4,
two int4,
three int4
);
create table first (
id int4 generated by default as identity primary key,
one int4,
two int4
);
create table second (
id int4 generated by default as identity primary key,
three int4,
first_id int4 references first(id)
);
I want to take each row from collector and create a row in the other two tables with the matching properties, and link second.first_id to first.id
Normally I’d do this via an INSERT INTO .. SELECT .. ON CONFLICT .. DO UPDATE SET statement, but adding that second table seems to break that pattern.
1
As per the comment: it sounds like a regular insert with two preceding CTE’s with returning clauses. I’ll take this down or edit if it turns out collector.two column isn’t meant to be unique but in the meanwhile, this seems to fit the description:
demo at db<>fiddle
with cte1 as(
insert into collector(one,two,three)values
( 1, 2, 3)
,(11,12,13)
,(21,22,23)
returning one,two,three)
,cte2 as(
insert into first(one,two)
select one,two
from cte1
returning id,one,two)
insert into second(three,first_id)
select cte1.three,cte2.id
from cte1 join cte2 using(two);
collector:
| id | one | two | three |
|---|---|---|---|
| 1 | 1 | 2 | 3 |
| 2 | 11 | 12 | 13 |
| 3 | 21 | 22 | 23 |
first:
| id | one | two |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 11 | 12 |
| 3 | 21 | 22 |
second:
| id | three | first_id |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 13 | 2 |
| 3 | 23 | 3 |
1