Postgresql - Inserting data into linked tables by Foreign Key

Question

Hello!

I’m having a problem inserting data into tables linked by a foreign key. I read in some places that there is a command "with" that helps in these situations, but I didn’t understand how to use.

I would like to join four tables that will be used to register, however, that all data be entered at once, in only one query, and that are associated with the last table, to facilitate future queries. Below follows the code of creation of the tables:

    CREATE TABLE participante
        (
          id serial NOT NULL,
          nome character varying(56) NOT NULL,
          CONSTRAINT participante_pkey PRIMARY KEY (id),
        );

    CREATE TABLE venda
    (
      id serial NOT NULL,
      inicio date NOT NULL,
      CONSTRAINT venda_pkey PRIMARY KEY (id)
    );

    CREATE TABLE item
    (
      id serial NOT NULL,
      nome character varying(256) NOT NULL,
      CONSTRAINT item_pkey PRIMARY KEY (id)
    );


    CREATE TABLE lances_vendas
    (
      id serial NOT NULL,
      venda_id integer NOT NULL,
      item_id integer NOT NULL,
      participante_id integer NOT NULL,
      valor numeric NOT NULL,
      CONSTRAINT lance_vendas_pkey PRIMARY KEY (id),
      CONSTRAINT lances_vendas_venda_id_fkey FOREIGN KEY (venda_id)
        REFERENCES venda (id),
      CONSTRAINT lances_vendas_item_id_fkey FOREIGN KEY (item_id)
        REFERENCES item (id),
      CONSTRAINT lances_vendas_participante_id_fkey FOREIGN KEY (participante_id)
        REFERENCES participante (id)
    );

Thanks in advance for your help and understanding.

Answer 1

Try the following:

WITH    
   venda AS (insert into venda(inicio) values (now()) returning id), 
   item as (insert into item(nome) values ('batata-frita') returning id), 
   participante as(insert into participante(nome) values ('Anselmo') returning id)

     insert into lances_vendas(venda_id, item_id, participante_id, valor) 
        select *, 100 from venda, item, participante;