---
source: https://iinventedlight.com/library/notes/postgres-ctes-with-queries.md
title: "PostgreSQL WITH queries (CTEs) and recursive queries"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL docs 7.8: WITH Queries (Common Table Expressions), PostgreSQL License; PostgreSQL Global Development Group, SELECT, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-05
as_of: 2026-11-05
confidence: medium
untested: "Steps and code were not run; only the fetched documentation page was read."
category: coding-references
topic_area: "code-backend: SQL common table expressions"
tags: sql, postgresql, cte, recursive, database
sources:
  - id: S1
    title: "PostgreSQL docs 7.8: WITH Queries (Common Table Expressions)"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/queries-with.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
  - id: S2
    title: "SELECT"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-select.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
    license: "PostgreSQL License"
---
# PostgreSQL CTEs (WITH queries)

## Short answer
`WITH` names a subquery you can reuse in one larger statement. Think of it as a temporary table that lives for that query only. [S1]

## Sketch
```sql
WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT customer_id, count(*) FROM recent GROUP BY 1;
```

## Points
- `WITH` attaches auxiliary statements to a larger query. [S1]
- Those auxiliaries act like temporary tables for one query. [S1]
- `RECURSIVE` lets a `WITH` query refer to its own output. [S1]
- In `SELECT` processing, every query in the `WITH` list is computed first. [S2]

## Claims
- id: C1
  claim: WITH writes auxiliary statements for a larger query.
  source: S1
  evidence: "WITH provides a way to write auxiliary statements for use in a larger query"
- id: C2
  claim: CTEs behave like temporary tables for one query.
  source: S1
  evidence: "temporary tables that exist just for one query"
- id: C3
  claim: RECURSIVE CTEs can refer to their own output.
  source: S1
  evidence: "a WITH query can refer to its own output"
- id: C4
  claim: SELECT computes every WITH list query first.
  source: S2
  evidence: "All queries in the WITH list are computed"

## Sources
- [S1] PostgreSQL docs 7.8: WITH Queries (Common Table Expressions). PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/queries-with.html . License: PostgreSQL License. Accessed 2026-11-05.
- [S2] SELECT. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-select.html . License: PostgreSQL License. Accessed 2026-11-05.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

Source notice: PostgreSQL documentation, Portions Copyright (c) 1996-2026, The PostgreSQL Global Development Group; Portions Copyright (c) 1994, The Regents of the University of California. Used under the PostgreSQL License (https://www.postgresql.org/about/licence/).