Postgres CTE 如何帮助将数据同时插入到多个表中？

使用 Postgres CTE 跨多个表并发插入数据

高效地将数据插入多个互连的数据库表对于管理复杂的数据关系至关重要。 PostgreSQL 的通用表表达式 (CTE)，特别是数据修改 CTE，提供了强大的解决方案。

考虑一个涉及三个表的场景：sample、sample1 和 sample2，定义如下：

<code class="language-sql">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)
);</code>

挑战在于重用一次插入生成的键以用于后续插入到相关表中。 数据修改 CTE 优雅地解决了这个问题：

<code class="language-sql">WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
   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;</code>

这种链式 CTE 方法可确保顺序插入，每个 INSERT 都取决于其前一个的成功。 使用 SELECT 而不是 VALUES 可保证下游表插入仅在前面的 INSERT 成功完成时才继续进行。

或者，更集中的方法使用单个 CTE 来定义数据行：

<code class="language-sql">WITH data(firstname, lastname, adddetails, value) AS (
   VALUES ('fai55', 'shaggk', 'ss', 'ss2')
   ),
ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT DISTINCT firstname, lastname
   FROM data
   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);</code>

此方法需要仔细处理重复的 (firstname, lastname) 组合。 并发写入注意事项也很重要，需要进一步调查（有关详细信息，请参阅其他资源）。

以上是Postgres CTE 如何帮助将数据同时插入到多个表中？的详细内容。更多信息请关注PHP中文网其他相关文章！