scribase
All posts
·Scribasepostgressqlbranching

Common table expressions (CTE) in Postgres: WITH, by example

How WITH queries work in Postgres: chained CTEs, MATERIALIZED vs NOT MATERIALIZED, recursive CTEs, and reviewing the SQL on a database branch.

A common table expression, or CTE, is a named SELECT that you introduce with WITH and then use as if it were a table. It is the same idea as a SQL subquery in the FROM clause, written so a person (or a coding agent) can see the steps in order.

WITH paid_orders AS (
  SELECT customer_id, id, total_cents
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, count(*) AS paid_count, sum(total_cents) AS paid_cents
FROM paid_orders
GROUP BY customer_id;

paid_orders exists only for this statement. It is not a temporary table you have to drop, and it is not a view you have to migrate. When the statement ends, the name is gone.

This article uses PostgreSQL, which is what every Scribase project runs. For the syntax alone, see the shorter CTE quick reference. The examples assume the same customers, orders and order_items tables as the subquery article. You can create them on a disposable branch and delete the branch afterwards.

Why write a CTE instead of a nested subquery

A derived table and a CTE can produce the same plan. You reach for a CTE when:

  • The inner query would be repeated. You write it once and select from it twice.
  • The steps are easier to review in order: filter, then aggregate, then join.
  • An agent generated the SQL and you want each step named in the pull request.
WITH paid AS (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
),
by_customer AS (
  SELECT customer_id, count(*) AS paid_count, sum(total_cents) AS paid_cents
  FROM paid
  GROUP BY customer_id
)
SELECT c.email, b.paid_count, b.paid_cents
FROM by_customer b
JOIN customers c ON c.id = b.customer_id
WHERE b.paid_cents >= 10000
ORDER BY b.paid_cents DESC;

Later CTEs can read earlier ones in the same WITH list. That is the usual way to keep a long statement linear instead of nested.

Postgres will often inline a CTE

In PostgreSQL 12 and later, a non-recursive CTE can be inlined into the main query, the way a subquery would be. The planner is not required to run the CTE in isolation first.

If you need the CTE to be computed once and held, say so:

WITH paid AS MATERIALIZED (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM paid
GROUP BY customer_id;

NOT MATERIALIZED asks the planner to inline when it can.

WITH paid AS NOT MATERIALIZED (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM paid
GROUP BY customer_id;

Use MATERIALIZED when the CTE is expensive and referenced more than once, or when you have measured that inlining made a worse plan. Do not add it by habit. EXPLAIN (ANALYZE, BUFFERS) on a realistic branch will tell you whether the CTE was inlined or scanned as a work table.

In PostgreSQL 11 and earlier, non-recursive CTEs were an optimization fence: they were always materialized. If you are reading an old blog post that says "a CTE always runs separately," check the version. Hosted Scribase projects run current Postgres, where inlining is the default behaviour.

Data-changing statements can start with WITH

A CTE is not limited to SELECT. You can attach one to INSERT, UPDATE or DELETE, and a CTE itself can contain those statements if you are moving rows in one transaction.

WITH stale_pending AS (
  SELECT id
  FROM orders
  WHERE status = 'pending'
    AND placed_at < now() - interval '7 days'
)
UPDATE orders o
SET status = 'cancelled'
FROM stale_pending r
WHERE o.id = r.id
RETURNING o.id, o.customer_id;

RETURNING is the clean way to see what changed. A later article in this series covers RETURNING on its own. For now, treat the whole statement as one transaction: it all commits, or it all rolls back.

This is also why a preview branch matters. An UPDATE with a CTE is still an UPDATE. Run it on a sanitized or snapshot branch, or on a local branch, not on a protected production database while you are still reading the plan.

Recursive CTEs, briefly

A recursive CTE names itself in the second half of a UNION ALL. The first half is the starting set; the second half is the step.

CREATE TABLE categories (
  id        bigint PRIMARY KEY,
  parent_id bigint REFERENCES categories (id),
  name      text NOT NULL
);

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE NOT c.id = ANY (t.path)   -- cycle guard
)
SELECT id, name, depth
FROM tree
ORDER BY path;

The NOT c.id = ANY (t.path) predicate is a cycle guard. Postgres has no built-in recursion limit, so without the guard a single bad row (a category that is its own ancestor) keeps the query running until statement_timeout cancels it or the work table fills the disk.

A later article in this series goes further on recursive CTEs. You do not need them for the everyday WITH used in application queries.

CTEs, temporary tables, and views

A CTE is a clause in one statement. A temporary table is a real table, visible for the rest of the session (or the transaction, if you created it that way), and you have to drop it or wait for the session to end. A view is a stored query in the schema, and creating it is a migration.

Use a CTE when the name only matters for this statement. Use a temporary table when you need indexes on the intermediate result, or when several statements in one session should reuse it. Use a view (or a materialized view) when many queries, over many days, should share the same definition.

On a Scribase preview branch you can try all three without ceremony. If the view or the extra index is worth keeping, it becomes part of the schema diff and can be merged. If it was only a scratch pad, delete the branch and it is gone. That is a better habit than leaving temporary objects in production "for now."

Agent-generated SQL tends to overuse either a six-level subquery or a string of temporary tables. A short stack of CTEs is usually the middle path: readable in the pull request, gone when the statement finishes, and still easy to EXPLAIN.

Row level security still applies

A CTE does not bypass row level security. The user or role that runs the statement sees only the rows the policies allow, including rows read or written inside the WITH clause. If you are testing a policy, the preview branch is also where you should run it, and Scribase can refuse a migration that would leave a public table without RLS.

That matters for the examples in this article. On a sanitized preview, emails and other configured columns are anonymized, but the policy still runs. If a CTE looks correct in a superuser session and empty as the app role, the policy, not the WITH clause, is the first place to look. The guides cover writing those policies.

A CTE does not replace an index, a constraint, or a branch

Naming a step does not make it cheap. If paid_orders filters on status and placed_at, you still want an index that matches that predicate. If a CTE is used to "upsert" rows, you still want a unique constraint and INSERT ... ON CONFLICT (see the upsert guide).

The same review loop as the subquery article applies:

  1. Create a preview (sanitized by default, schema_only if you will load fixtures, snapshot only when you need exact rows).
  2. Run the statement and, if it is heavy, EXPLAIN (ANALYZE, BUFFERS).
  3. If you added an index or a constraint, scribase branch diff and a dry-run merge.
  4. Merge schema only, after you approve it. Data does not come back from the branch unless you write that script.
  5. Delete the preview, or let the TTL do it.

See branch per task and Postgres branching. Branches are included in the project price, from the free project up through Hobby at five dollars a month. Protected branches refuse delete, reset and restore, which is what you want for production.

Locally:

scribase branch new try-ctes
psql "$(scribase branch url try-ctes)"
scribase branch delete try-ctes

Common mistakes

  • Assuming a CTE always runs once. In modern Postgres it may be inlined. If you need "run once," use MATERIALIZED and measure.
  • Nesting a CTE that still hides a join fan-out. count(*) after a one-to-many join will surprise you whether the join lives in a CTE or a subquery.
  • Forgetting the alias or the closing parenthesis, which is the usual syntax error the first time you write one.
  • Writing a recursive CTE without a cycle guard.
  • Running a WITH ... UPDATE against production to "just see the rows." Use RETURNING on a branch.

If you arrived here first, read SQL subquery for scalar, IN, EXISTS and derived-table forms. Next in the series are UNION and UNION ALL and ORDER BY DESC. The product guides for preview databases and row level security are in the Scribase guides.

To try the examples on a hosted project, start at scribase.com. One project is free, with no card and no time limit. A temporary project via npx scribase init --temp needs no account.