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中文網其他相關文章！