使用 Postgres 一次在 3 个表中插入数据 [英] Insert data in 3 tables at a time using Postgres
问题描述
我想通过一个查询将数据插入到 3 个表中.
我的表格如下所示:
I want to insert data into 3 tables with a single query.
My tables looks like below:
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.
如何实现这一目标?
推荐答案
使用 数据修改 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冲突.)
这种方式也更短、更快.
Each INSERT depends on the one before. SELECT instead of VALUES makes sure nothing is inserted in subsidiary tables if no row is returned from a previous INSERT. (Since Postgres 9.5+ you might add an ON CONFLICT.)
It's also a bit shorter and faster this way.
通常，在一个地方提供完整的数据行会更方便:
Typically, it's more convenient to provide complete data rows in one place:
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 表达式，其中数据类型是从目标表派生的.见:
You may need explicit type casts in a stand-alone VALUES expression - as opposed to a VALUES expression attached to an INSERT where data types are derived from the target table. See:
如果多行可以带有相同的(firstname, lastname)，您可能需要折叠第一个INSERT的重复项:
If multiple rows can come with identical (firstname, lastname), you may need to fold duplicates for the first INSERT:
...
INSERT INTO sample (firstname, lastname)
SELECT DISTINCT firstname, lastname FROM data
...
您可以使用(临时)表代替 CTE data 作为数据源.
You could use a (temporary) table as data source instead of the CTE data.
将其与表中 (firstname, lastname) 的 UNIQUE 约束和查询中的 ON CONFLICT 子句结合起来可能是有意义的.
It would probably make sense to combine this with a UNIQUE constraint on (firstname, lastname) in the table and an ON CONFLICT clause in the query.
相关:
这篇关于使用 Postgres 一次在 3 个表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
