PostgreSQL多INSERT ...多列返回 [英] 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
的子表.
我可以将新插入的行的user_id
提取到table1
(父级)中:
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
INSERT
s (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 resultinguser_id
- obviously from a sequence or some other default. It's actually theuser_id
fromtable3
, but that's the same unless you have some triggers or other magic interfering.有关数据修改(又称可写")CTE的更多信息:
More about data-modifying (a.k.a. "writable") CTEs:
这篇关于PostgreSQL多INSERT ...多列返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!