PostgreSQL 資料修改 CTE 會將多項操作合併成一個共用快照的陳述式。交易中的個別陳述式會依序執行，並可讀取先前的寫入。請根據各操作需要如何取得輸入來選擇。

對於在 Mac 的 Terminal 中工作的開發者，具體任務是判斷合成文章工作流程應該放在一個 SQL 陳述式中，還是拆成數個交易查詢。下方範例展示可見性差異、適用的 CTE 工作流程，以及完整的 node-postgres 交易。

將操作合併至 CTE 時會有什麼變化？

資料修改 CTE 會在 WITH 中包含 INSERT、UPDATE、DELETE，或在目前的 PostgreSQL 中包含 MERGE。其各個部分共用同一個快照；實際修改的順序無法預測。若其他部分需要變更後的資料列，請透過 RETURNING 傳遞。依照由上而下的順序撰寫 CTE，並不能確立程序式的執行順序。PostgreSQL：WITH 查詢

在 BEGIN 和 COMMIT 中的個別 SQL 陳述式各自有陳述式邊界。在 PostgreSQL 預設的 Read Committed 隔離層級下，每個陳述式都會取得新的快照，並可看見同一交易先前所做的變更。其他交易已提交的變更也可能在陳述式之間變得可見。PostgreSQL：交易隔離

這兩種方法都能提供全有或全無的操作。如果沒有明確的交易區塊，PostgreSQL 會在隱含交易中執行個別陳述式；明確交易則會將多個陳述式組成一個整體。資料修改 CTE 也可以是較大交易中的一個陳述式。PostgreSQL：交易

需求 合適的起點
將修改後的資料列直接傳入另一個資料表 使用 RETURNING 的 CTE
在前一個陳述式變更資料表後讀取該表 交易中的循序陳述式
透過兩個明確階段變更同一資料列 循序陳述式，或將最終計算合併至一次更新
檢查中間結果後再決定下一個查詢 搭配應用程式邏輯的循序陳述式
減少在獨立請求之間等待的時間 將傳輸方式與 SQL 語意分開評估

這份決策指南依循文件記載的可見性規則。先描述相依性：下一個操作需要的是回傳的資料關係、重新讀取資料表，還是由 JavaScript 做出的決定？這個問題比計算有多少操作能放進 WITH 更有助於選擇。

如何在本機重現可見性差異？

使用 psql 中既有的 PostgreSQL 18 連線，或在 Mac 上使用 SQL 編輯器。在同一個工作階段中執行以下完整腳本。它只會建立暫存資料表，使用一篇合成文章，並以 ROLLBACK 結尾。預期值是根據文件推導而來；準備本文時並未執行資料庫範例。

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

在第一個查詢中，資料表掃描會讀取 draft，而 CTE 的輸出包含 edited。下一個陳述式會從資料表讀取 edited。這說明了文件記載的差異：在修改陳述式中讀取目標資料表，與讀取 CTE 回傳的資料列並不相同。PostgreSQL：WITH 查詢

最後兩次更新刻意在不同陳述式中變更同一資料列。將它們合併為並列的修改 CTE 並不受支援：PostgreSQL 無法可靠地在單一陳述式中對同一資料列進行兩次修改。參照較早的 CTE 也無法解除這項限制。PostgreSQL：SELECT

什麼時候適合使用資料修改 CTE？

將選取的文章資料列移至封存區，是一個實用的關聯式任務：刪除操作會找出確切的資料列，其回傳的資料關係則提供插入操作所需的資料。使用 DELETE 時，RETURNING 會公開已刪除資料列的內容；使用 UPDATE 時，其預設輸出包含更新後的值。PostgreSQL：從修改的資料列回傳資料

這個獨立範例會自行提供資料表和輸入資料。請在前一個範例結束後，於同一個工作階段中執行。

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

插入操作會直接使用 moved。修改用的 WITH 必須附加在最上層陳述式上。即使沒有使用輸出，修改用的 CTE 仍會執行至完成；省略 RETURNING 會移除可用的結果關係，但不會取消修改。PostgreSQL：WITH 查詢

交易邊界也會影響失敗情況：若插入失敗，移動操作就無法成功完成。在明確交易中發生錯誤後，請執行 ROLLBACK。不要把成功收到中間資料列視為外層交易已提交的證明。PostgreSQL：交易

node-postgres 應如何執行相依陳述式？

如果工作流程需要先檢查一個結果，再送出下一個查詢，請在同一個用戶端上循序呼叫 await。node-postgres 要求交易中的每個陳述式都使用同一個用戶端；透過不同的 pool.query() 呼叫分派交易步驟，無法確保連線持續一致。node-postgres：交易

必要條件包括 Node.js、npm，以及既有的本機 PostgreSQL 18 資料庫連線。在 Terminal 的 Node 專案目錄中執行 npm install pg。將下方完整程式儲存為 article-transaction.mjs，然後執行 node article-transaction.mjs。new Client() 會使用一般的 PostgreSQL 連線環境變數；如果預設值不符合你的資料庫，請設定為既有資料庫的連線資訊。node-postgres：連線

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

try {
  await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

預期輸出的資料列為 id: 1 和 stage: 'ready'。提交時會刪除暫存資料表。已使用 Node.js 檢查 JavaScript 語法，但未執行資料庫操作。

查詢呼叫會依照 node-postgres 的參數化查詢 API，使用 $1、$2 和 $3 分別傳入值。每次呼叫都包含一個 SQL 陳述式。node-postgres：查詢

檢查 rowCount 可讓非預期的零資料列更新在提交前明確失敗。rowCount 回報命令處理的資料列數，而 rows 則包含回傳的記錄；沒有 RETURNING 的更新可能會影響資料列，但回傳空的 rows 陣列。node-postgres：pg.Result

批次處理陳述式會改變其 SQL 語意嗎？

批次描述的是如何送出工作。它不會將多個 SQL 陳述式轉變成一個資料修改 CTE。

PostgreSQL 的 simple-query 通訊協定接受以分號分隔的陳述式。如果沒有明確的交易控制，同一個 simple Query 訊息中的陳述式會在隱含交易中執行。發生錯誤時，後續陳述式會停止執行。extended protocol 的 Parse 訊息只接受一個陳述式，因此，以分號分隔的批次無法普遍取代個別的參數化查詢。PostgreSQL：訊息流程

明確的批次也需要在批次外部處理錯誤：如果在結尾的 ROLLBACK 執行前發生錯誤，就不會執行該回滾操作。請另外送出回滾請求，以結束失敗的交易。PostgreSQL：Simple Query 中的多個陳述式

目前的 node-postgres 文件也說明了選擇性啟用的流水線處理。它會在等待每個回應前送出請求，而 PostgreSQL 會依序處理這些請求。文件建議在交易的下一個查詢取決於較早結果時，循序呼叫 await。流水線處理會改變請求時序，但不會建立共用的 CTE 快照。node-postgres：流水線處理

選擇時應參考哪些版本和隔離限制？

本文查閱的目前 PostgreSQL 文件標示為第 18 版。PostgreSQL 16 允許在資料修改 CTE 主體中使用 INSERT、UPDATE 和 DELETE，但不允許在其中使用 MERGE。第 17 版文件已納入 MERGE，其版本說明也記錄了 MERGE RETURNING 的新增。對於 PostgreSQL 16 工作流程，請使用受支援的修改陳述式或個別陳述式，不要直接複製較新版本的 CTE 語法。PostgreSQL 16：WITH 查詢、PostgreSQL 17：WITH 查詢、PostgreSQL 17 版本說明

Repeatable Read 不會讓循序陳述式的行為等同於並列 CTE。它會保留由第一個非交易控制陳述式建立的交易快照，但後續陳述式仍可看見該交易先前自行寫入的資料。使用此隔離層級的應用程式也必須準備在發生序列化失敗後重試交易。PostgreSQL：Repeatable Read 隔離層級

MATERIALIZED 和 NOT MATERIALIZED 無法解決資料修改 CTE 的可見性問題。對於非純副作用的 CTE，PostgreSQL 會忽略 NOT MATERIALIZED。這些規劃器控制項無法建立對同一資料列進行循序寫入的步驟。PostgreSQL：SELECT

當回傳的資料列自然會成為下一個操作的輸入時，請選擇資料修改 CTE。當後續工作需要先前的資料表變更或應用程式決策時，請選擇循序陳述式。最佳化請求時序時，請保留這種相依性；若多個陳述式必須一起完成，請使用明確交易。

來源