← All notes

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

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/).

@misc{iil_note_pk94xdd6,
  author = {{iinventedlight}},
  title = {{PostgreSQL WITH queries (CTEs) and recursive queries}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-ctes-with-queries/},
  note = {Permanent ID iil:note:pk94xdd6. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-ctes-with-queries.md.