如何在單一查詢中將資料插入多個相關的 Postgres 表中？

跨多個相關 Postgres 表的單查詢資料插入

本指南解決了使用單一 SQL 查詢將資料插入多個互連的 Postgres 表中的挑戰，特別是當需要從初始插入中檢索主鍵並將其用作後續插入中的外鍵時。

問題：在單一查詢中有效地將資料插入三個或更多相關表中，確保維護正確的外鍵關係。

解：利用資料修改 CTE

通用表表達式 (CTE) 提供了一個優雅的解決方案。 資料修改 CTE 允許順序 INSERT 操作，其中每個後續插入都依賴前一個插入的結果。

實作範例：

下面示範了使用資料修改 CTE 將資料插入到三個表格 (sample、sample1、sample2) 中：

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;

說明：

1. ins1：在 sample 表中插入一行，並傳回新產生的 id（主鍵）作為 sample_id.
2. ins2：利用 sample_id 中的 ins1 向 sample1 插入一行，回傳產生的 user_id。
3. 最終 INSERT：使用 user_id 中的 ins2 將資料插入 sample2。

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

此方法同時處理多個資料行：

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

透過在 data CTE 中定義多行，可以有效地插入多行。

重要注意事項：

• 外鍵約束：確保定義適當的外鍵約束以維護引用完整性。
• ON 衝突子句： 利用 ON CONFLICT 管理潛在的重複鍵錯誤。
• 併發： 注意並發資料寫入期間潛在的競爭條件。

這種綜合方法提供了一種強大而高效的方法來管理單一查詢中跨相關 Postgres 表的資料插入。

以上是如何在單一查詢中將資料插入多個相關的 Postgres 表中？的詳細內容。更多資訊請關注PHP中文網其他相關文章！