PostgreSQL - Inserting data into tables linked by Foreign Key

Question

PostgreSQL - Inserting data into tables linked by Foreign Key

Navigation

#1 by (2 votes)

1

Hello

I have a data insertion problem in tables linked by foreign keys. I have read in some places that there is a "with" command that helps in these situations, but I did not quite understand how it is used.

I would like to put together four tables that will be used to make a record, however, that all the data were inserted at once, in a single query, and that they were associated with the last table, to facilitate future consultations. Here is the code for creating 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.

sql query postgresql

asked by anonymous 11.01.2017 / 06:30

1 answer

Login Form n = 1 in the while condition returns no value

score 2 · Accepted Answer

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;