sql - PostgreSQL multi INSERT...RETURNING 多列

Question

我正在构建一个以 Postgres 9.3 作为后端的数据库，有 3 个表:

table1 (user_id, username, name, surname, emp_date)
table2 (pass_id, user_id, password)
table3 (user_dt_id, user_id, adress, city, phone)

可以看出table2和table3是table1的子表。
我可以提取 table1(父级)中新插入行的 user_id:

INSERT INTO "table1" (default,'johnee','john','smith',default) RETURNING userid;

我需要将新提取的 ID(来自 table1)插入到 table2 和 table3 的 user_id 列中以及那些表独有的其他数据。基本上是 3 X INSERT ...
我该怎么做？

Answer 1

使用data-modifying CTEs链接您的三个 INSERT。像这样:

WITH ins1 AS (
   INSERT INTO table1 (username, name,  surname)
   VALUES ('johnee','john','smith')
   RETURNING user_id
   )
, ins2 AS (
   INSERT INTO table2 (user_id, password)
   SELECT ins1.user_id, 'secret'
   FROM   ins1                            -- nothing to return here
   )
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM   ins1
RETURNING user_id;

• 通常最好为 INSERT 添加一个列定义列表(特殊情况除外)。否则，如果表结构发生变化，您的代码可能会以令人惊讶的方式中断。

• 我省略了您只需输入 DEFAULT 的列。默认值会自动插入。更短，结果相同。

• 最后的可选 RETURNING 返回结果 user_id - 显然来自序列或其他一些默认值。它实际上是 table3 中的 user_id，但除非您有一些触发器或其他魔术干扰，否则它是相同的。

有关数据修改(又名“可写”)CTE 的更多信息:

• Are SELECT type queries the only type that can be nested?