CTEs (WITH Clause)
A CTE (Common Table Expression) defines a named temporary result set within a query using the WITH clause. It improves readability by naming complex subqueries and can be referenced multiple times in the main query.
CTEs are like naming a sticky note. Instead of rewriting a complex formula every time, you name it 'monthly_sales' and refer to that name throughout the query. Each reference is a lookup to the same sticky note.
Syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. Multiple CTEs: WITH a AS (...), b AS (...) SELECT ... . PostgreSQL treats CTEs as optimization fences by default in older versions — the CTE is materialized once, and the planner cannot push predicates inside it. Since PostgreSQL 12, CTEs are inlined by default (treated like subqueries, allowing predicate pushdown) unless they are recursive or contain side effects. WITH MATERIALIZED forces materialization; WITH NOT MATERIALIZED forces inlining.
CTE vs Subquery: for non-recursive, non-side-effect CTEs in PostgreSQL 12+, they're equivalent — the planner inlines CTEs. In older versions, CTEs were always materialized, making them useful as optimization barriers (sometimes intentional to prevent planner mistakes). CTE materialization can hurt performance if the CTE produces many rows that are then filtered, but can help if the CTE result is expensive and referenced multiple times. Writeable CTEs: INSERT/UPDATE/DELETE in a CTE with RETURNING — chain data modifications: WITH deleted AS (DELETE FROM old_table RETURNING *) INSERT INTO archive SELECT * FROM deleted.
CTEs make complex queries readable by naming intermediate steps. In PostgreSQL 12+, non-recursive CTEs without side effects are inlined by default (same as subqueries; recursive CTEs are still materialized). I use CTEs for readability and to structure multi-step logic. Writable CTEs (INSERT/UPDATE/DELETE with RETURNING) let me chain DML operations atomically — delete from one table and insert into another in a single statement.
Assuming CTEs always create a temporary table and are materialized once. In PostgreSQL 12+, non-recursive CTEs are inlined — predicate pushdown applies. In pre-12, they were always materialized, which could hurt or help performance.