Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table A
(   id  INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    str TEXT NOT NULL
);

insert into A(str)
values ('a'),
       ('b'),
       ('c');

create table B
(   id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    a  INT REFERENCES A
);

CREATE TABLE



INSERT 0 3



CREATE TABLE


with select1 as (select id from A where str = 'b'),
     select2 as (select id from A where str = 'c'),
     insert0 as (insert into A(str) values ('d') returning id)
insert into B(a)
select idv 
from select1,
     select2,
     insert0,
lateral(values(select1.id),
              (select2.id),
              (select2.id),
              (1/*constant*/),
              (insert0.id) )AS vals(idv)
returning *;


id
a


1
2

2
3

3
3

4
1

5
4



INSERT 0 5


select A.*,B.* from A inner join B on A.id=B.a;


id
str
id
a


2
b
1
2

3
c
2
3

3
c
3
3

1
a
4
1

4
d
5
4



SELECT 5