Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 90 ยท Querying with PostgreSQL Features

CTEs, Materialization and Recursive Queries

Learn PostgreSQL WITH queries: MATERIALIZED and NOT MATERIALIZED, writable CTEs, and recursive CTEs with the SEARCH and CYCLE clauses for trees and graphs.

A common table expression, or CTE, gives a name to a query so that you can use it like a temporary table inside one statement. If you have never used one, start with CTEs in SQL and Recursive CTEs. This lesson covers what is special in PostgreSQL: how the planner treats a CTE, the MATERIALIZED hint, CTEs that change data, and two clauses, SEARCH and CYCLE, that make recursive queries on trees and graphs much shorter.

PostgreSQL's WITH is more powerful than in most databases, and a few of its behaviors surprise people who learned CTEs elsewhere. Knowing them helps you write readable queries without paying a hidden performance price.

What runs live here. The editors on this page run on SQLite (sql.js), which supports ordinary and recursive CTEs and the MATERIALIZED hint. Writable CTEs, SEARCH and CYCLE exist only in PostgreSQL, so they appear as read-only blocks, each with a live stand-in that produces the same result by hand.

Four things a PostgreSQL CTE can do

Name a query Break a big statement into readable steps.
Recurse Walk trees and graphs of any depth.
Write data INSERT, UPDATE and DELETE inside WITH.
Control the plan MATERIALIZED or NOT MATERIALIZED.
1 / 2

Plain CTEs and the planner

A CTE is declared with WITH name AS (query). Here is a small sales table and a CTE used twice: once to compute a total per region, once to compare each region against the average of all regions.

SQL One CTE, used twice

Press Run (or Ctrl + Enter) to see the result here.

The totals are south 350, north 200, east 90 and west 100, so the average is 185 and diff_from_average is 165, 15, -95 and -85. Writing the grouping once and using it twice is the main reason CTEs exist.

Inlining and materialization

PostgreSQL has two ways to run a CTE:

  • Inlined. The planner treats the CTE like a subquery in FROM and merges it into the outer query. A WHERE in the outer query can then reach inside, use an index, and skip rows that were never needed.
  • Materialized. The CTE is run to the end once, the rows are kept in a temporary store, and the outer query reads that store like a table. The outer filters cannot reach inside.

Before PostgreSQL 12 every CTE was materialized, which made it an "optimization fence". Many old blog posts say CTEs are always slow for that reason. Today the rule is:

  • A CTE that is referenced once, is not recursive and has no side effects is inlined by default.
  • A CTE referenced more than once, a recursive one, or one that changes data is materialized.

You can override the default per CTE:

SQL
-- Force the CTE to run once and be reused (a fence)
WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT ... FROM recent ...;

-- Allow merging even though the CTE is used twice
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT ... FROM recent a JOIN recent b ON ...;

Use MATERIALIZED when the CTE is expensive and used several times, or when you want a fence on purpose, for example to stop the planner from choosing a bad plan by pushing a filter inside. Use NOT MATERIALIZED when the CTE is cheap and an outer filter should reach an index inside it. If you do not have a measured reason, leave the default alone.

SQLite accepts the same two keywords, so you can try the syntax live. The result is identical either way, because a hint changes how the query runs, never what it returns:

SQL MATERIALIZED and NOT MATERIALIZED give the same rows

Press Run (or Ctrl + Enter) to see the result here.

To see which way PostgreSQL ran a CTE, use EXPLAIN. A materialized CTE shows up as a CTE Scan node, while an inlined one disappears into the plan of the outer query. More on reading plans in EXPLAIN and EXPLAIN ANALYZE.

Writable CTEs

In PostgreSQL, the query inside WITH can be an INSERT, UPDATE or DELETE as long as it has a RETURNING clause to hand rows to the next step. This lets you do several changes as one atomic statement. The classic case is archiving: delete old rows and insert them somewhere else without a gap between the two steps.

This runs only on PostgreSQL:

SQL
WITH removed AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '2 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM removed;

A few rules that are worth knowing:

  • All sub-statements in one WITH see the same snapshot of the tables, as they were when the whole statement began. They cannot see each other's changes to the tables, only the rows passed along through RETURNING.
  • Data-modifying CTEs are always executed, even if the outer query never reads them. They run to completion exactly once.
  • The order in which two modifying sub-statements run is not defined. Do not rely on it.
  • Do not let two sub-statements modify the same row; the outcome is unpredictable.

A second common use is inserting a parent row and its children together, passing the generated key along:

SQL
WITH new_order AS (
  INSERT INTO orders (customer_id) VALUES (42)
  RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity)
SELECT id, 7, 2 FROM new_order;

SQLite does not allow INSERT or DELETE inside WITH, so we cannot run these here. The same effect on SQLite takes two statements inside a transaction. See INSERT ON CONFLICT and RETURNING for how RETURNING itself works.

Recursive CTEs for trees

A recursive CTE has an anchor query, then UNION or UNION ALL, then a recursive query that reads the CTE itself. This page assumes you know that shape. Here is a category tree stored as an adjacency list, walked from the roots down. The path column builds a readable breadcrumb and depth counts levels.

SQL Walking a category tree

Press Run (or Ctrl + Enter) to see the result here.

Sorting by the text path puts every parent directly before its children, which is a depth-first order. It works here, but it has a weakness: if a name contains a character that sorts before the separator, the order can break. PostgreSQL gives you a better tool.

SEARCH: ordering without hand-built paths

SEARCH DEPTH FIRST BY or SEARCH BREADTH FIRST BY adds a hidden ordering column to the CTE, so you do not need to build and sort a path string yourself. This is PostgreSQL 14 and later, and it is read-only here:

SQL
WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1
  FROM categories AS c
  JOIN tree AS t ON c.parent_id = t.id
)
SEARCH DEPTH FIRST BY name SET ordercol
SELECT * FROM tree ORDER BY ordercol;
  • DEPTH FIRST lists a node, then everything below it, then its next sibling, like the indented output above.
  • BREADTH FIRST lists all of one level before the next, which is what you want to find the shortest connection.
  • BY name says how siblings are ordered. The column you name is stored in the ordering value.
  • SET ordercol names the new column. You still write ORDER BY ordercol, because the clause does not sort the final result for you.

The live stand-in for breadth first is simply ordering by depth:

SQL Breadth first by hand

Press Run (or Ctrl + Enter) to see the result here.

You get four levels: Books and Music at depth 0, Fiction and Science at depth 1, then Fantasy, Mystery and Physics, and finally Epic fantasy.

Graphs and cycles

A tree has no loops, but a graph can. If A links to B and B links back to A, a naive recursive CTE never ends because it keeps finding "new" rows forever. The traditional guard is a path of visited nodes, checked on every step:

SQL Avoiding a loop with a visited path

Press Run (or Ctrl + Enter) to see the result here.

Starting at A, the query reaches B (1 hop), C (2 hops), D (3 hops) and E (4 hops). The link from C back to A is skipped, because A is already in the path. The instr check is a string search, so it only works for single-character labels like these. With real ids, you would use delimiters such as ,1,2, or, in PostgreSQL, an array.

CYCLE: the built-in guard

PostgreSQL 14 added a CYCLE clause that does this bookkeeping for you:

SQL
WITH RECURSIVE walk AS (
  SELECT 'A'::text AS node, 0 AS hops
  UNION ALL
  SELECT l.dst, w.hops + 1
  FROM links AS l
  JOIN walk AS w ON l.src = w.node
)
CYCLE node SET is_cycle USING path
SELECT node, hops, is_cycle FROM walk;
  • CYCLE node means "watch the values of node; if a row repeats one already seen on its own path, flag it".
  • SET is_cycle creates a boolean column that is true on the row that closed the loop. The recursion does not continue from that row, so the query ends.
  • USING path names an array column that records the visited values. Before PostgreSQL 17 this part is required; newer versions let you leave it out when you do not need the path.

In the output you would see the extra row A with is_cycle = true at 3 hops. You keep that row instead of dropping it, which is useful when your goal is to report cycles, for example to find circular dependencies. Filter with WHERE NOT is_cycle if you only want the clean paths.

Be careful with UNION versus UNION ALL in recursive queries. Plain UNION removes duplicate rows, which stops some loops, but only when whole rows repeat. As soon as a depth or path column makes each row unique, UNION no longer protects you. A cycle guard, or a depth limit such as WHERE depth < 20, is the safe habit.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL 12 and later, when is a CTE inlined into the outer query by default?

    A simple, side-effect-free CTE used once is merged into the outer query so filters and indexes can be used. Recursive CTEs, CTEs used several times and data-modifying CTEs are materialized unless you override it.
  2. 2. What does WITH x AS MATERIALIZED (...) do?

    MATERIALIZED is a planner hint. It creates an optimization fence and a stored result for that statement only; nothing is saved permanently.
  3. 3. What is required for an INSERT, UPDATE or DELETE inside a WITH query to pass rows to the next step?

    The rows that flow to the outer query come from RETURNING. Without it the statement still runs but yields nothing to read.
  4. 4. What does the CYCLE clause add to a recursive CTE?

    CYCLE tracks the watched columns along each path, sets your flag column on the repeating row and stops recursing from it. It replaces the hand-built visited-path check.
  5. 5. Which clause gives you a hidden column to order a recursive tree depth first or breadth first?

    SEARCH DEPTH FIRST BY ... SET col or SEARCH BREADTH FIRST BY ... SET col adds an ordering column. You still need an ORDER BY on that column in the final query.

Summary

  • A CTE names a query inside one statement. Since PostgreSQL 12, a side-effect-free CTE used once is inlined; others are materialized.
  • AS MATERIALIZED forces a fence and one evaluation, AS NOT MATERIALIZED allows merging. Change the default only with a measured reason.
  • Data-modifying CTEs need RETURNING, always run once, and all share the snapshot from the start of the statement.
  • Recursive CTEs walk trees and graphs. Order depth first or breadth first with SEARCH, and stop loops with CYCLE or a visited path.
  • Live editors here ran the portable parts on SQLite; writable CTEs, SEARCH and CYCLE are PostgreSQL only and shown read-only.

Next: Designing Tables the PostgreSQL Way.