SQL 使用 Postgres 一次在 3 个表中插入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20561254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert data in 3 tables at a time using Postgres
提问by Faisal
I want to insert data into 3 tables with a single query.
My tables looks like below:
我想通过一个查询将数据插入到 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)
);
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.
但是，如果我运行单个查询，它们只会将值返回给我，而我无法立即在下一个查询中重用它们。
How to achieve this?
如何实现这一目标？
回答by Erwin Brandstetter
Use data-modifying CTEs:
使用数据修改 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;
Each INSERTdepends on the one before. SELECTinstead of VALUESmakes sure nothing is inserted in subsidiary tables if no row is returned from a previous INSERT. (Related: the ON CONFLICTclause in Postgres 9.5+)
It's also a bit shorter and faster this way.
每个都INSERT取决于前一个。SELECT而不是VALUES确保没有插入子公司表，如果没有行从以前返回INSERT。（相关：ON CONFLICTPostgres 9.5+ 中的子句）
这种方式也更短更快。
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);
You may need explicit type casts in a stand-alone VALUESexpression - as opposed to a VALUESexpression attached to an INSERTwhere data types are derived from the target table.
您可能需要在独立VALUES表达式中进行显式类型转换- 而不是VALUES附加到INSERTwhere 数据类型从目标表派生的表达式。
If multiple rows can come with identical (firstname, lastname), you may need to fold duplicates for the first INSERT:
如果多行可以带有相同的(firstname, lastname)，您可能需要折叠第一行的重复项INSERT：
...
INSERT INTO sample (firstname, lastname)
SELECT DISTINCT firstname, lastname FROM data
...
You could use a (temporary) table as data source instead of the CTE data.
您可以使用（临时）表作为数据源而不是 CTE data。
Related:
有关的：
回答by a_horse_with_no_name
Something like this
像这样的东西
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');
As the generated id from the insert into sample2is not needed, I removed the returningclause from the last insert.
由于sample2不需要从 insert into 生成的 id ，我returning从最后一个 insert 中删除了该子句。
回答by Denis de Bernardy
Typically, you'd use a transaction to avoid writing complicated queries.
通常，您会使用事务来避免编写复杂的查询。
http://www.postgresql.org/docs/current/static/sql-begin.html
http://www.postgresql.org/docs/current/static/sql-begin.html
http://dev.mysql.com/doc/refman/5.7/en/commit.html
http://dev.mysql.com/doc/refman/5.7/en/commit.html
You could also use a CTE, assuming your Postgres tag is correct. For instance:
你也可以使用 CTE，假设你的 Postgres 标签是正确的。例如：
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;
回答by DaImTo
You could create an after insert trigger on the Sample table to insert into the other two tables.
您可以在 Sample 表上创建一个 after insert 触发器以插入到其他两个表中。
The only issue i see with doing this is that you wont have a way of inserting adddetails it will always be empty or in this case ss. There is no way to insert a column into sample thats not actualy in the sample table so you cant send it along with the innital insert.
我在这样做时看到的唯一问题是，您无法插入 adddetails，它将始终为空或在这种情况下为 ss。无法将一列插入到样本表中实际上并不存在的样本中，因此您不能将其与初始插入一起发送。
Another option would be to create a stored procedure to run your inserts.
另一种选择是创建一个存储过程来运行您的插入。
You have the question taged mysql and postgressql which database are we talking about here?
您有标记为 mysql 和 postgressql 的问题，我们在这里谈论的是哪个数据库？
