CTEs for readable queries
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
break a complex query into named steps with WITH.
Planned topics
- A CTE (common table expression) is a named subquery:
WITH name AS (...). - Chain several CTEs; each can read the earlier ones.
- Readability, not speed, is the main gain. In PostgreSQL 12+ a CTE is inlined unless it is referenced twice or has side effects;
MATERIALIZED/NOT MATERIALIZEDforce the choice (verify version before recording). - Name each step after what it holds, not how it is computed.
Draft outline details
Demonstration
WITH paid AS (
SELECT * FROM orders WHERE status = 'PAID'
), per_customer AS (
SELECT customer_id, sum(total_cents) AS spent FROM paid GROUP BY customer_id
)
SELECT * FROM per_customer WHERE spent > 100000;Check your understanding
Is a CTE always an optimization fence?
Show answer
No; since PostgreSQL 12 it is usually inlined.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.