使用Postgres同时向3个表中插入数据

Question

我希望能够通过一个查询语句将数据插入到3个表中。
我的表结构如下：

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

每次插入我都会得到一个密钥，我需要在下一个表中插入该密钥。
我的查询是：

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;

但如果我运行单个查询，它们只会把值返回给我，我无法立即在下一个查询中重用它们。

如何实现这一点？

- Faisal

Answer 1

使用数据修改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 sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;

每个INSERT都依赖于前面的一个。使用SELECT而不是VALUES可以确保在先前的INSERT中没有返回行时，不会将任何内容插入到子表中。（自Postgres 9.5+以来，您可以添加ON CONFLICT。）这种方法也更简短、更快速。

通常，在一个地方提供完整的数据行更加方便：

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

db<>fiddle 这里

在独立的VALUES表达式中，您可能需要显式类型转换 - 而不是附加到INSERT的VALUES表达式，其中数据类型从目标表派生。请参见：

• 更新多行时强制转换NULL类型

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

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

您可以使用一个（临时）表作为数据源，而不是 CTE data。

可能最好将其与表中的（firstname，lastname）上的UNIQUE约束以及查询中的ON CONFLICT子句相结合。

这并不解决可能出现的并发写入问题。对此，请参见：

• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

• 函数中的 SELECT 或 INSERT 是否容易出现竞态条件？

- Erwin Brandstetter

---

谢谢回复。如果插入失败，我可以添加事务回滚吗？是的，我该怎么做？ - Faisal

这是一条SQL语句。您可以将多个语句捆绑成一个事务，但不能拆分此语句。还有Denis在他的评论中所说的话。我在答案中添加了一些链接。 - Erwin Brandstetter

@mmcrae：是的，你可以。相关链接：http://dba.stackexchange.com/questions/151199/why-cant-rows-inserted-in-a-cte-be-updated-in-the-same-statement/151217#151217 - Erwin Brandstetter

@AdamHughes：确实，在多个地方sample_id和user_id混淆了。这个例子的列名相当具有误导性。已经修复、澄清并添加了一个fiddle。 - Erwin Brandstetter

不禁想到这是PostgreSQL的一个非常酷的功能！ - Code4R7

Answer 2

类似这样的东西

with first_insert as (
   insert into sample(firstname,lastname) 
   values('fai55','shaggk') 
   RETURNING id
), 
second_insert as (
  insert into sample1( id ,adddetails) 
  values
  ( (select id from first_insert), 'ss')
  RETURNING user_id
)
insert into sample2 ( id ,adddetails) 
values 
( (select user_id from first_insert), 'ss');

由于从插入到sample2生成的id不需要，因此我从最后一个插入语句中删除了returning子句。

- user330315

---

我喜欢这种使用select内部值的方法。它更加一致，同时也可以在with语句中省略返回别名。 - mattdlockyer

Answer 3

通常情况下，您可以使用事务来避免编写复杂的查询。

http://www.postgresql.org/docs/current/static/sql-begin.html

http://dev.mysql.com/doc/refman/5.7/en/commit.html

如果您的Postgres标记正确，您也可以使用CTE。例如：

with sample_ids as (
  insert into sample(firstname, lastname)
  values('fai55','shaggk')
  RETURNING id
), sample1_ids as (
  insert into sample1(id, adddetails)
  select id,'ss'
  from sample_ids
  RETURNING id, user_id
)
insert into sample2(id, user_id, value)
select id, user_id, 'val'
from sample1_ids
RETURNING id, user_id;

- Denis de Bernardy

---

谢谢，我该如何在这个查询中实现事务，如果有任何插入失败，我可以执行回滚。 - Faisal

然后你再重新开始，当然要先纠正查询，因为整个事务（或cte）都会被回滚。顺便说一句，如果您的插入偶尔失败，那么您可能正在做一些错误的事情。唯一合理的插入失败情况是在并发事务期间遇到重复的唯一键的upsert场景中，即使这样，如果需要使事情变得牢固，您也可以获得咨询锁定或表锁定。 - Denis de Bernardy

Answer 4

你可以在Sample表上创建一个after insert触发器，以便插入另外两个表中。这样做的唯一问题是，你无法插入addetails，它将始终为空或者在此情况下为空。在Sample表中没有办法插入一列，因此你不能将其与初始插入一起发送。另一种选择是创建一个存储过程来运行你的插入操作。

你将这个问题标记为mysql和postgressql，我们正在谈论哪个数据库？

- Linda Lawton - DaImTo