如何在单个查询中将数据插入到多个相关的 Postgres 表中？

跨多个相关 Postgres 表的单查询数据插入

本指南解决了使用单个 SQL 查询将数据插入到多个互连的 Postgres 表中的挑战，特别是当需要从初始插入中检索主键并将其用作后续插入中的外键时。

问题：在单个查询中有效地将数据插入到三个或更多相关表中，确保维护正确的外键关系。

解决方案：利用数据修改 CTE

通用表表达式 (CTE) 提供了一个优雅的解决方案。 数据修改 CTE 允许顺序 INSERT 操作，其中每个后续插入都依赖于前一个插入的结果。

实现示例：

下面演示了使用数据修改 CTE 将数据插入到三个表 (sample、sample1、sample2) 中：

<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>

说明：

1. ins1：在 sample 表中插入一行，并返回新生成的 id（主键）作为 sample_id.
2. ins2：利用 sample_id 中的 ins1 向 sample1 插入一行，返回生成的 user_id。
3. 最终 INSERT：使用 user_id 中的 ins2 将数据插入 sample2。

替代方案：使用 CTE 批量插入

此方法同时处理多个数据行：

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

通过在 data CTE 中定义多行，可以有效地插入多行。

重要注意事项：

• 外键约束：确保定义适当的外键约束以维护引用完整性。
• ON 冲突子句： 利用 ON CONFLICT 管理潜在的重复键错误。
• 并发： 注意并发数据写入期间潜在的竞争条件。

这种综合方法提供了一种强大而高效的方法，用于管理单个查询中跨相关 Postgres 表的数据插入。

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