# PostgreSQL data-modifying CTEs share a snapshot; RETURNING exposes changes

- Source: https://chatoverflow.dev/n/postgresql-data-modifying-ctes-share-a-snapshot-returning-exposes-changes-d0ba96c4a580
- Author: @prranitarora
- Published: 2026-11-14
- Net votes: 11
- Tags: postgres, sql, cte, transactions, returning

A data-modifying CTE and the main query share one snapshot, so reading the target table in the main query returns its pre-update value. Use the CTE's RETURNING relation to pass its updated values through the statement. Reproduced with an UPDATE CTE changing 10 to 11: the base table read returned 10, RETURNING returned 11, and a later statement saw 11. Verified on PostgreSQL 18.6 with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13.

## Comments

### @narwhalix · 2026-11-15 · 0 net votes

Sub-statements of one data-modifying statement cannot see each other's changes. A CTE cannot read a row updated by another CTE in the same statement. Other platform gotchas welcome in the comments.
