PostgreSQL - Insert data into multiple tables simultaneously

Question

I am having a data insertion problem in tables linked by foreign key. I have read in some places that there is a "with" command that helps in these situations, but I do 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)
    );

Laurenz Albe · Accepted Answer · 2022-03-08T07:14:59.330

22

The idea is to write WITH clauses that contain INSERT ... RETRUNING to return the generated keys. Then these “views for a single query” can be used to insert those keys into the referencing tables.

WITH par_key AS
        (INSERT INTO participante (nome) VALUES ('Laurenz') RETURNING id),
     ven_key AS
        (INSERT INTO venda (inicio) VALUES (current_date) RETURNING id),
     item_key AS
        (INSERT INTO item (nome) VALUES ('thing') RETURNING id)
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valor)
   SELECT ven_key.id, item_key.id, par_key.id, numeric '3.1415'
   FROM par_key, ven_key, item_key;

edited Mar 08 '22 at 07:14

answered Jan 11 '17 at 16:05

Laurenz Albe

209,280
17
206
263

This example, in combination with https://stackoverflow.com/questions/50298216/postgresql-insert-with-multiple-selects, was very helpful. – Mike Finch Mar 08 '22 at 01:45
upvoted! for a users table with user_id as primary, accounts table with acount_id as primary and user_accounts table with a auto generated id and user_id and account_id as composite unique constraint, i am guessing this works until say the account could not be created for some reason. I am guessing that if one statement fails, everything else will fail – PirateApp Nov 04 '22 at 09:00

score 2 · Answer 2 · answered Jan 11 '17 at 16:42

2

I know that you requested a single query, but you may still want to consider using a transaction:

BEGIN;
INSERT INTO participante (nome) VALUES ('Laurenz');
INSERT INTO venda (inicio) VALUES (current_date);
INSERT INTO item (nome) VALUES ('thing');
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valer)
VALUES (currval('venda_id_seq'), currval('item_id_seq'), currval('participante_id_seq'), 3.1415);
COMMIT;

The transaction ensures that any new row in participante, venda and item leave the value of currval('X') unchanged.

answered Jan 11 '17 at 16:42

Fabian Pijcke

2,920
25
29

1

Sequences are not transactional, so it isn't safe to rely on transaction here. – fpietka Jan 11 '17 at 17:35
How is it unsafe? The side effect of nextval() is immediately visible outside of the transaction and that's why it's (supposed to be, as you don't seem convinced) safe to use its result. – Fabian Pijcke Jan 11 '17 at 17:51
The following discussion might make things clearer for you :p http://stackoverflow.com/questions/2095917/sequences-not-affected-by-transactions – Fabian Pijcke Jan 11 '17 at 17:56
That is why I wouldn't recommend that answer, as you are not guaranteed that `currval()` give you the right value. Maybe unsafe wasn't the right word. – fpietka Jan 11 '17 at 18:11
You are guaranteed ... currval() is fixed for the session ... I'll get you the documentation part that says it, just wait a minute :-) – Fabian Pijcke Jan 11 '17 at 20:22
2

Here it is: "Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did." (https://www.postgresql.org/docs/current/static/functions-sequence.html) – Fabian Pijcke Jan 11 '17 at 20:23
1

@fpietka: Your understanding is wrong `currval()` **will** give the correct answer here. – Feb 12 '18 at 09:55
to avoid these generated-id issues, they could use application-generated random IDs of some sort probably UUIDs – francojposa May 14 '22 at 21:11

score 2 · Answer 3 · answered Feb 12 '18 at 09:53

You could create a function to do that job. Take a look at this example:

CREATE OR REPLACE FUNCTION import_test(p_title character varying, p_last_name character varying, p_first_name character varying, p_house_num integer, p_street character varying, p_zip_code character varying, p_city character varying, p_country character varying)
RETURNS integer
LANGUAGE plpgsql
AS
$body$
DECLARE

    address_id uuid;
    parent_id uuid;
    ts timestamp;

BEGIN

    address_id := uuid_generate_v4();
    parent_id := uuid_generate_v4();
    ts := current_timestamp;

    insert into address (id, number, street, zip_code, city, country, date_created) values (address_id, p_house_num, p_street, p_zip_code, p_city, p_country, ts);
    insert into person (id, title, last_name, first_name, home_address, date_created) values (parent_id, p_title, p_last_name, p_first_name, address_id, ts);

RETURN 0;
END;
$body$
VOLATILE
COST 100;

COMMIT;

Note how the generated UUID for the address (first insert) is used in the person-record (second insert)

Usage:

SELECT import_test('MR', 'MUSTERMANN', 'Peter', 'john2@doe.com', 54, 'rue du Soleil', '1234', 'Arlon', 'be');
SELECT import_test('MS', 'MUSTERMANN', 'Peter 2', 'peter2@yahoo.com', 55, 'rue de la Lune', '56789', 'Amnéville', 'fr');

PostgreSQL - Insert data into multiple tables simultaneously

3 Answers3

Linked