使用Postgres一次在3个表中插入数据 [英] Insert data in 3 tables at a time using Postgres

问题描述

我想通过一个查询将数据插入3个表中.
我的表格如下所示:

I want to insert data into 3 tables with a single query.
My tables looks like below:

CREATE TABLE sample (
   id        bigserial PRIMARY KEY,
   lastname  varchar(20),
   firstname varchar(20)
);

CREATE TABLE sample1(
   user_id    bigserial PRIMARY KEY,
   sample_id  bigint REFERENCES sample,
   adddetails varchar(20)
);

CREATE TABLE sample2(
   id      bigserial PRIMARY KEY,
   user_id bigint REFERENCES sample1,
   value   varchar(10)
);

每次插入时我都会得到一个密钥作为回报，我需要将该密钥插入到下表中.
我的查询是:

I will get a key in return for every insertion and I need to insert that key in the next table.
My query is:

insert into sample(firstname,lastname) values('fai55','shaggk') RETURNING id;
insert into sample1(sample_id, adddetails) values($id,'ss') RETURNING user_id;
insert into sample2(user_id, value) values($id,'ss') RETURNING id;

但是，如果我运行单个查询，它们只会向我返回值，而我不能立即在下一个查询中重复使用它们.

But if I run single queries they just return values to me and I cannot reuse them in the next query immediately.

如何实现?

推荐答案

使用 修改数据的CTE :

WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
-- ON     CONFLICT DO NOTHING            -- optional addition in Postgres 9.5+
   RETURNING id AS user_id
   )
, ins2 AS (
   INSERT INTO sample1 (user_id, adddetails)
   SELECT user_id, 'ss' FROM ins1
   -- RETURNING user_id                  -- only if used in turn
   )
INSERT INTO sample2 (user_id, value)     -- same here
SELECT user_id, 'ss' FROM ins1;

每个INSERT都取决于之前的那个.如果没有从前一个INSERT返回任何行，请确保SELECT而不是VALUES不会在子表中插入任何内容. (相关: ON CONFLICT 子句在Postgres 9.5及更高版本中)
这样也更短，更快.

Each INSERT depends on the one before. SELECT instead of VALUES makes sure nothing is inserted in subsidiary tables if no row is returned from a previous INSERT. (Related: the ON CONFLICT clause in Postgres 9.5+)
It's also a bit shorter and faster this way.

通常， 在一处提供完整的数据行 更加方便:

Typically, it's more convenient to provide complete data rows in one place:

WITH data(firstname, lastname, adddetails, value) AS (
   VALUES                                 -- provide data here
      (text 'fai55', text 'shaggk', text 'ss', text 'ss2')  -- see below
       --  more?                          -- works for multiple input rows
   )
, ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT firstname, lastname FROM data   -- DISTINCT? see below
   ON     CONFLICT DO NOTHING             -- requires UNIQUE constraint
   RETURNING firstname, lastname, id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, adddetails
   FROM   data
   JOIN   ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, value
FROM   data
JOIN   ins1 USING (firstname, lastname)
JOIN   ins2 USING (sample_id);

您可能需要在独立的VALUES表达式中进行显式类型转换-与附加到INSERT的VALUES表达式相反，在INSERT表达式中数据类型是从目标表派生的.

You may need explicit type casts in a stand-alone VALUES expression - as opposed to a VALUES expression attached to an INSERT where data types are derived from the target table.

如果多个行可以带有相同的(firstname, lastname)，则可能需要折叠第一个INSERT的重复项:

If multiple rows can come with identical (firstname, lastname), you may need to fold duplicates for the first INSERT:

...
INSERT INTO sample (firstname, lastname)
SELECT DISTINCT firstname, lastname FROM data
...

您可以使用(临时)表代替CTE data作为数据源.

You could use a (temporary) table as data source instead of the CTE data.

相关:

• 如何在ON CONFLICT中使用RETURNING在PostgreSQL中?
• SELECT或INSERT是否处于容易出现竞争条件的函数中?

• How to use RETURNING with ON CONFLICT in PostgreSQL?
• Is SELECT or INSERT in a function prone to race conditions?

这篇关于使用Postgres一次在3个表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！