PostgreSQL - 同时向多个表插入数据

Question

我在由外键链接的表中遇到了数据插入问题。我在一些地方读到过有一个“with”命令可以帮助解决这些情况，但我不太明白它如何使用。

我想组合四个表，用于创建记录，然而，所有数据都会一次性插入，以单个查询的形式，并且它们将与最后一个表关联，以便未来的查询更加便捷。以下是创建表的代码：

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

- Oak

Answer 1

这个想法是编写包含INSERT ... RETURNING的WITH子句，以返回生成的键。然后可以使用这些“单个查询的视图”将这些键插入到引用表中。

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;

- Laurenz Albe

---

这个例子和 https://dev59.com/E6vka4cB1Zd3GeqPrFD1 结合起来，非常有帮助。 - Mike Finch

对于一个具有user_id作为主键的用户表，具有acount_id作为主键的账户表以及具有自动生成的id和user_id和account_id作为组合唯一约束条件的用户账户表，我猜想这个方案可以工作，直到某些原因导致无法创建账户。我猜想如果一个语句失败了，其他所有语句都会失败。 - PirateApp

Answer 2

你可以创建一个函数来完成这项工作。看一下这个例子：

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;

请注意第一个插入操作生成的UUID如何在第二个插入操作中用于person记录。

用法：

SELECT import_test('MR', 'MUSTERMANN', 'Peter', '[e-mail]', 54, 'rue du Soleil', '1234', 'Arlon', 'be');
SELECT import_test('MS', 'MUSTERMANN', 'Peter 2', '[e-mail]', 55, 'rue de la Lune', '56789', 'Amnéville', 'fr');

- yglodt

Answer 3

我知道您请求的是单个查询，但您可能仍然需要考虑使用事务：

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;

该事务确保参与者、销售和项目中的任何新行都不会改变currval('X')的值。

- Fabian Pijcke

---

序列不是事务性的，因此在这里依赖事务是不安全的。 - fpietka

它为什么不安全？nextval()的副作用会立即在事务之外可见，这就是为什么使用其结果是安全的（应该是安全的，因为您似乎并不信服）。 - Fabian Pijcke

因为这返回的是会话本地值，所以它可以给出可预测的答案，无论其他会话是否在当前会话执行 nextval。 - Fabian Pijcke

@fpietka：你的理解是错误的，currval() 会 在这里给出正确的答案。 - user330315

为避免这些生成的 ID 问题，他们可以使用应用程序生成的随机 ID，可能是 UUID。 - francojposa