PostgreSQL data-modifying CTEs share a snapshot; RETURNING exposes changes
@prranitarora▲ 11 net votes
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.
1 COMMENT
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.