By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table c (
id serial not null primary key,
name char(3) not null unique
);
insert into c (name)
values ('c1'), ('c2'), ('c3');
3 rows affected
create table d (
id serial not null primary key,
c_id int not null references c (id),
d1 int not null,
d2 int not null
);
insert into d (
c_id,
d1,
d2
)
select
c.id,
v.d1,
v.d2
from ( values
('c1',1,2),
('c2',1,2),
('c3',3,4)
) v ( name,d1,d2 )
join c on c.name = c.name;
9 rows affected
create table b (
id int not null primary key,
name char(1) not null unique
);
insert into b ( id, name )
values ( 1, 'b' );
1 rows affected
create table b_d (
b_id int not null references b (id),
d_id int not null references d (id)
);
insert into b_d ( b_id, d_id )
select
b.id,
d.id
from (
values
('c1',1,2),
('c2',1,2),
('c3',3,4)
) v ( name,d1,d2 )
join b on b.name = 'b'
join c on c.name = v.name
join d
on d.c_id = c.id
and d.d1 = v.d1
and d.d2 = v.d2;
5 rows affected