How to insert a postgres record into two tables at once, so that the second has a field with BIGSERIAL from the first?

Question

PostgreSQL

Essence:
there is a POSTGRESQL 9.3 base and there are two tables, banners and keywords.
Both have the first field id BIGSERIAL, and then a set of fields to taste
, since the banner can be made for several keywords, it was decided to store the banner_id field in the keywords table, which should contain the id field from the banners table
and, in principle, I would not take a steam bath, if there was no need to insert 500 thousand banners and a million keywords at a time, and do it regularly.
therefore I search for the most effective way to interpose in record in both tables at once. Relatively speaking, in one request, part of the data is inserted into banners, the second part - into keywords, and in keywords in the banner_id field the value of the id field of the newly inserted record from banners is inserted.

Answer 1

your query is from the series "how to get the last insert ID simultaneously in two tables"
with one beautiful query without INSERT ... SELECT nothing can be done before that,
but there are triggers,
insert all data into one table, trigger on AFTER INSERT - copy part to another, from delete first (UPDATE) or at the end or trigger from second table (not sure if it will work, but should)
trigger example
stackoverflow.com/questions/12343984/insert-trigge...

Answer 2

in general for "heap" of the data it is better to use COPY www.postgresql.org/docs/current/static/sql-copy.html
and than such variant confuses you?

create table banners(banner_id serial primary key, value text);
create table keywords(keyword_id serial primary key, banner_id int references banners);


do $$
  declare 
    _banner_id int;
    i int;
  begin


  FOR i IN 1..10 LOOP 
  
    INSERT INTO banners (value) VALUES ('My banner ' || i) RETURNING banners.banner_id INTO _banner_id;
    
    FOR i IN 1..100 LOOP 
    
      INSERT INTO keywords(banner_id) VALUES (_banner_id);
    END LOOP;
  END LOOP;
  

  end;
$$;