A PostgreSQL data-modifying CTE combines operations into one statement with a shared snapshot. Separate statements in a transaction run in sequence and can read earlier writes. Choose based on how each operation needs to receive its input.

For a developer working in Terminal on a Mac, the concrete task is deciding whether a synthetic article workflow belongs in one SQL statement or several transactional queries. The examples below show the visibility difference, a suitable CTE workflow, and a complete node-postgres transaction.

What changes when you combine operations into a CTE?

A data-modifying CTE contains an INSERT, UPDATE, DELETE, or, in current PostgreSQL, MERGE inside WITH. Its components share a snapshot; their physical modification order is unpredictable. Pass changed rows through RETURNING when another component needs them. Writing CTEs from top to bottom does not establish procedural execution order. PostgreSQL: WITH Queries

Separate SQL statements inside BEGIN and COMMIT have statement boundaries. At PostgreSQL’s default Read Committed isolation level, each statement takes a new snapshot and can see earlier changes made by its own transaction. Other transactions’ commits can also become visible between statements. PostgreSQL: Transaction Isolation

Both approaches can provide an all-or-nothing operation. PostgreSQL executes an individual statement inside an implicit transaction when no explicit transaction block exists; an explicit transaction groups multiple statements. A data-modifying CTE can also be one statement within a larger transaction. PostgreSQL: Transactions

Requirement Appropriate starting point
Feed modified rows directly into another table A CTE with RETURNING
Read a table after an earlier statement changes it Sequential statements in a transaction
Change the same row through two deliberate stages Sequential statements, or combine the final calculation into one update
Inspect an intermediate result before choosing the next query Sequential statements with application logic
Reduce time spent waiting between independent requests Evaluate transport options separately from SQL semantics

This decision aid follows the documented visibility rules. Start by describing the dependency: does the next operation need a returned relation, a fresh table read, or a decision made by JavaScript? That question is more useful than counting how many operations fit inside WITH.

How can you reproduce the visibility difference locally?

Use an existing PostgreSQL 18 connection in psql or a SQL editor on your Mac. Run the following complete script in one session. It creates only a temporary table, uses one synthetic article, and ends with ROLLBACK. The expected values are derived from the documentation; the database examples were not executed during preparation.

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;

In the first query, the table scan reads draft, while the CTE’s output contains edited. The next statement reads edited from the table. This illustrates the documented distinction between reading a target table within the modifying statement and reading the CTE’s returned rows. PostgreSQL: WITH Queries

The final two updates deliberately change the same row in separate statements. Combining them into sibling modifying CTEs would be unsupported: PostgreSQL does not reliably perform two modifications of the same row within one statement. Referencing an earlier CTE does not remove that restriction. PostgreSQL: SELECT

When is a data-modifying CTE a good fit?

Moving selected article rows into an archive is a useful relational task: the deletion identifies the exact rows, and its returned relation supplies the insertion. With DELETE, RETURNING exposes the deleted row contents; with UPDATE, its default output contains the updated values. PostgreSQL: Returning Data from Modified Rows

This standalone example supplies its own tables and input. Run it in one session after the previous example finishes.

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;

The insertion consumes moved directly. The modifying WITH must attach to the top-level statement. A modifying CTE runs once to completion even if its output is unused; omitting RETURNING removes its usable result relation, not its modification. PostgreSQL: WITH Queries

The transaction boundary also matters for failure: an insertion failure prevents the move from completing successfully. In an explicit transaction, issue ROLLBACK after an error. Do not treat successfully receiving intermediate rows as proof that a surrounding transaction has committed. PostgreSQL: Transactions

How should node-postgres run dependent statements?

For a workflow that examines one result before sending the next query, use sequential await calls on one client. node-postgres requires the same client for every statement in a transaction; dispatching transaction steps through separate pool.query() calls does not establish that connection continuity. node-postgres: Transactions

Prerequisites are Node.js, npm, and an existing local PostgreSQL 18 database connection. In your Node project directory in Terminal, run npm install pg. Save the complete program below as article-transaction.mjs, then run node article-transaction.mjs. new Client() uses the usual PostgreSQL connection environment variables; configure them for your existing database if defaults do not match. node-postgres: Connecting

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

The expected printed row has id: 1 and stage: 'ready'. The temporary table is dropped at commit. JavaScript syntax was checked with Node.js; database execution was not performed.

The query calls pass values separately using $1, $2, and $3, following node-postgres’s parameterized query API. Each call contains one SQL statement. node-postgres: Queries

Checking rowCount makes an unexpected zero-row update an explicit failure before commit. rowCount reports the command’s processed-row count, whereas rows contains returned records; an update without RETURNING can affect rows while returning an empty rows array. node-postgres: pg.Result

Does batching statements change their SQL semantics?

A batch describes how work is submitted. It does not turn several SQL statements into one data-modifying CTE.

PostgreSQL’s simple-query protocol accepts semicolon-separated statements. Without explicit transaction control, statements in one simple Query message execute in an implicit transaction. An error stops the remaining statements. The extended protocol’s Parse message accepts only one statement, so a semicolon-separated batch is not a general replacement for separate parameterized queries. PostgreSQL: Message Flow

An explicit batch also needs error handling outside the batch: if an error occurs before a trailing ROLLBACK, that rollback is never reached. Send a separate rollback request to end the failed transaction. PostgreSQL: Multiple Statements in a Simple Query

Current node-postgres documentation additionally describes opt-in pipelining. It sends requests without waiting for each response, while PostgreSQL processes them in order. The documentation recommends sequential await calls when a transaction’s next query depends on an earlier result. Pipelining changes request timing; it does not create a shared CTE snapshot. node-postgres: Pipelining

Which version and isolation limits should guide the choice?

The current PostgreSQL documentation retrieved for this article identifies version 18. PostgreSQL 16 permits INSERT, UPDATE, and DELETE as modifying CTE bodies, but excludes MERGE there. Version 17 documentation includes MERGE, and its release notes document the addition of MERGE RETURNING. For a PostgreSQL 16 workflow, use supported modifying statements or separate statements instead of copying newer CTE syntax. PostgreSQL 16: WITH Queries, PostgreSQL 17: WITH Queries, PostgreSQL 17 Release Notes

Repeatable Read does not make sequential statements behave like sibling CTEs. It retains a transaction snapshot established by the first non-transaction-control statement, but later statements still see the transaction’s own earlier writes. Applications using this isolation level must also be prepared to retry transactions after serialization failures. PostgreSQL: Repeatable Read Isolation Level

MATERIALIZED and NOT MATERIALIZED do not provide a workaround for modifying CTE visibility. PostgreSQL ignores NOT MATERIALIZED for CTEs that are not side-effect-free. Those planner controls cannot establish a sequence of writes to the same row. PostgreSQL: SELECT

Choose a modifying CTE when returned rows naturally form the next operation’s input. Choose sequential statements when later work needs earlier table changes or an application decision. Preserve that dependency when optimizing request timing, and use an explicit transaction whenever several statements must complete together.

Sources