Writeable CTEs: Atomic Data Pipelines

SELECT into Python, then INSERT, then DELETE — three round trips, with a real window where data exists in both places or neither. One writeable CTE closes that window completely.

A CTE is not limited to SELECT: DELETE, INSERT, and UPDATE can all appear inside a WITH clause, each with its own RETURNING clause exposing the rows it affected as a named result the rest of the statement can build on — exactly like a read-only CTE, except the "query" being named is a write. Chaining several of these together turns a multi-step data pipeline that would otherwise need several round trips (and several places for a partial failure to leave data in an inconsistent state) into one single SQL statement, atomic by construction: either the whole statement commits, or none of it does.\n\nA specific rule matters once several data-modifying CTEs coexist in one statement: every one of them reads the same snapshot of the database as it stood when the statement began — a DELETE step earlier in the same statement does not become visible to a plain SELECT reading that same table later in the identical statement. This is not a bug or an edge case to work around; it is what makes the whole statement's behavior fully predictable regardless of the order its CTEs happen to be listed in.

A 3-Step Atomic Pipeline

WITH deleted AS (
  DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *
),
archived AS (
  INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *
),
logged AS (
  INSERT INTO sql_wcte_log (action, moved_count) SELECT 'archive_orders', count(*) FROM archived RETURNING *
)
SELECT * FROM logged;

 id |     action     | moved_count |          moved_at
----+----------------+-------------+----------------------------
  1 | archive_orders |           5 |  2026-06-26 16:02:40.332593

One statement, one round trip: 5 old orders deleted, the exact same 5 rows inserted into the archive, and a summary row logged — all three, or none of them.

The Same-Snapshot Rule, Proven Directly

WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *)
SELECT count(*) AS still_visible_to_snapshot FROM sql_wcte_orders;
-- still_visible_to_snapshot: 10

SELECT count(*) AS after_statement FROM sql_wcte_orders;
-- after_statement: 5

Inside the same statement as the DELETE, a plain SELECT against the same table still counts all 10 rows — the delete has not yet become visible to it, because every part of one statement reads the same snapshot. Only once the statement finishes does a fresh query see the real, post-delete count of 5.

A Deliberate Error, Rolling Back Everything

-- with a row already present in the archive table with id = 3, causing a conflict:
WITH deleted AS (...), archived AS (INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *), logged AS (...)
SELECT * FROM logged;

ERROR:  duplicate key value violates unique constraint "sql_wcte_archive_pkey"
DETAIL:  Key (id)=(3) already exists.

SELECT count(*) FROM sql_wcte_orders;  -- 10, not 5 — the delete was rolled back too
SELECT count(*) FROM sql_wcte_log;     --  0        — the log write never happened either

The archive step's failure rolled back the delete that had already logically happened earlier in the same statement, and prevented the log write that would have happened after it — genuine atomicity across all three operations.

Writeable CTE

A CTE (WITH clause) whose body is a data-modifying statement — DELETE, INSERT, or UPDATE — rather than a plain SELECT, exposing the affected rows via RETURNING as a named result the rest of the statement can reference, exactly like a read-only CTE's output. Several writeable CTEs can be chained in a single statement, letting a multi-step write pipeline execute as one atomic unit.

Same-snapshot visibility

Every part of a single SQL statement — including every CTE within it, whether reading or writing — sees the same consistent snapshot of the database as it existed when the statement began. A DELETE earlier in the statement does not make its effect visible to a plain SELECT against the same table later in that same statement; only after the whole statement completes does a subsequent, separate statement see the change.

🔗 Build the 3-Step Atomic Archive Pipeline

Chain a DELETE, an INSERT, and a log INSERT through writeable CTEs in a single statement.

psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *), archived AS (INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *), logged AS (INSERT INTO sql_wcte_log (action, moved_count) SELECT 'archive_orders', count(*) FROM archived RETURNING *) SELECT * FROM logged;"

student@lab:~$ psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *), archived AS (INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *), logged AS (INSERT INTO sql_wcte_log (action, moved_count) SELECT 'archive_orders', count(*) FROM archived RETURNING *) SELECT * FROM logged;" SET id | action | moved_count | moved_at ----+----------------+-------------+---------------------------- 1 | archive_orders | 5 | 2026-06-26 16:02:40.332593 (1 row)

👁️ Prove the Same-Snapshot Rule Directly

Top up a few fresh old-dated rows, then inside one statement with a DELETE CTE, check whether a plain SELECT against the same table sees the deletion — then check again after the statement completes.

psql -U postgres -d beer_db -c "INSERT INTO sql_wcte_orders VALUES (11, DATE '2023-01-01', 110), (12, DATE '2023-01-01', 120), (13, DATE '2023-01-01', 130);"

psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *) SELECT count(*) AS still_visible_to_snapshot FROM sql_wcte_orders;"

psql -U postgres -d beer_db -c "SELECT count(*) AS after_statement FROM sql_wcte_orders;"

student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO sql_wcte_orders VALUES (11, DATE '2023-01-01', 110), (12, DATE '2023-01-01', 120), (13, DATE '2023-01-01', 130);" SET INSERT 0 3 student@lab:~$ psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *) SELECT count(*) AS still_visible_to_snapshot FROM sql_wcte_orders;" SET still_visible_to_snapshot ---------------------------- 8 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) AS after_statement FROM sql_wcte_orders;" SET after_statement ------------------ 5 (1 row)

💥 Force a Real Error and Confirm Everything Rolls Back

Top up one more old-dated row, insert a conflicting row into the archive table ahead of time, then run the full pipeline and confirm the delete and log steps are undone too.

psql -U postgres -d beer_db -c "INSERT INTO sql_wcte_orders VALUES (14, DATE '2023-01-01', 140);" -c "INSERT INTO sql_wcte_archive VALUES (14, DATE '2023-01-01', 140);"

psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *), archived AS (INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *), logged AS (INSERT INTO sql_wcte_log (action, moved_count) SELECT 'archive_orders', count(*) FROM archived RETURNING *) SELECT * FROM logged;"

psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_wcte_orders;" -c "SELECT count(*) FROM sql_wcte_log;"

student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO sql_wcte_orders VALUES (14, DATE '2023-01-01', 140);" -c "INSERT INTO sql_wcte_archive VALUES (14, DATE '2023-01-01', 140);" SET INSERT 0 1 INSERT 0 1 student@lab:~$ psql -U postgres -d beer_db -c "WITH deleted AS (DELETE FROM sql_wcte_orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year' RETURNING *), archived AS (INSERT INTO sql_wcte_archive SELECT * FROM deleted RETURNING *), logged AS (INSERT INTO sql_wcte_log (action, moved_count) SELECT 'archive_orders', count(*) FROM archived RETURNING *) SELECT * FROM logged;" SET ERROR: duplicate key value violates unique constraint "sql_wcte_archive_pkey" DETAIL: Key (id)=(14) already exists. student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_wcte_orders;" -c "SELECT count(*) FROM sql_wcte_log;" SET count ------- 6 (1 row) count ------- 1 (1 row)

Lab 3.3.6 complete. Writeable CTEs, proven atomic end to end:\n\n\n 3-step pipeline in 1 round trip : ✅ delete, archive, log — all real\n Same-snapshot rule proven directly : ✅ 10 mid-statement, 5 after\n Deliberate error, full rollback : ✅ delete AND log both undone\n

Enable JavaScript to run the live terminal and track your progress.