A common table expression (CTE) names the result of an auxiliary SQL statement so a subsequent primary statement can use it. Defined with WITH, it breaks complex queries into smaller, readable pieces and behaves like a temporary table or view limited to that statement. section 3.3 section 3.6
Use CTEs to:
- Filter and prepare data before aggregation, ranking, or other processing. section 3.3.1
- Chain several transformations, allowing each later CTE to reference earlier ones. section 3.3.2
- Modify data with INSERT, UPDATE, DELETE, or MERGE and pass returned rows to the primary statement. section 3.3 section 3.3.3
Rank fully played songs
WITH plays_cte AS ( SELECT s.title, s.duration FROM streaming.plays p JOIN streaming.songs s ON p.song_id = s.id WHERE p.play_start_time::DATE BETWEEN '2024-09-15' AND '2024-09-16' AND p.play_duration = s.duration ) SELECT title, COUNT(*) AS play_count FROM plays_cte GROUP BY title ORDER BY play_count DESC; The result ranks Song D first with four plays, followed by Song A with three and Song G with two. Replace the CTE filter and primary aggregation to produce a least-popular ranking. section 3.3.1
Chain CTEs for a business rule
One CTE can collect songs played for less than half their duration; a second can count distinct users and total plays; the primary query can retain only songs heard by at least three users. This separates filtering, aggregation, and selection into readable stages. section 3.3.2
Modify and inspect data in one statement
WITH updated_play AS ( UPDATE streaming.plays SET play_duration = 200 WHERE id = 30 RETURNING song_id, play_duration ) SELECT s.title, s.duration, CASE WHEN up.play_duration = s.duration THEN 'Moved Up the Rank' ELSE 'Rank Not Changed' END AS rank_change_status FROM updated_play up JOIN streaming.songs s ON s.id = up.song_id; RETURNING supplies the changed row to the primary query, which reports whether the song reached its full duration. section 3.3.3
Treat CTEs primarily as a clarity tool, not a guaranteed execution boundary. PostgreSQL may fold a SELECT CTE into the surrounding query or transform it; compare plans with EXPLAIN ANALYZE when performance matters. Data-modifying CTEs execute even when unreferenced, and sibling CTEs run concurrently, so their changes are not visible to one another unless a later CTE reads the modifying CTE's RETURNING output. section 3.3.1 section 3.3.3
WITH cte_name AS (
auxiliary_statement
)
primary_statement
Use the compact WITH template to identify the auxiliary statement, its name, and the primary statement that consumes its result. section 3.3
