PostgreSQL 多 INSERT...RETURNING 多列 [英] PostgreSQL multi INSERT...RETURNING with multiple columns

问题描述

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

I`m building a database with Postgres 9.3 as a backend, having 3 tables:

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:

As can be seen table2 and table3 are child tables of table1.
I can extract the user_id of a newly inserted row in table1 (parent):

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

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

I need to insert the newly extracted id (from table1) into user_id columns of table2 and table3 along with other data unique for those tables. Basically 3 X INSERT ...
How do I do that?

推荐答案

使用 数据修改 CTE 以链接您的三个 INSERT.像这样:

Use data-modifying CTEs to chain your three INSERTs. Something like this:

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 添加一个列定义列表(特殊情况除外).否则，如果表结构发生变化，您的代码可能会以令人惊讶的方式中断.

  • It's typically best to add a column definition list for INSERTs (except for special cases). Else, if the table structure changes, your code might break in surprising ways.

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

    I omitted columns where you would just enter DEFAULT. Defaults are inserted automatically. Shorter, same result.

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

    The final, optional RETURNING returns the resulting user_id - obviously from a sequence or some other default. It's actually the user_id from table3, but that's the same unless you have some triggers or other magic interfering.

    更多关于数据修改(又名可写")CTE:

    More about data-modifying (a.k.a. "writable") CTEs:

    • 是 SELECT 类型查询唯一可以嵌套的类型?

    这篇关于PostgreSQL 多 INSERT...RETURNING 多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！