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
);

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

CREATE TABLE

CREATE TABLE

with inserted_into_a as (
    insert into A(str)
    values ('a'),
           ('b'),
           ('c')
    returning id,str)
insert into B(a) select id from inserted_into_a;

INSERT 0 3

select * from a;
select * from b;

id
str

1
a

2
b

3
c

SELECT 3

id
a

1
1

2
2

3
3

SELECT 3