Multiple CTE in single query

by

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

The manual:

If RECURSIVE is specified, it allows a SELECT subquery to
reference itself by name.

Bold emphasis mine. And, even more insightful:

Another effect of RECURSIVE is that WITH queries need not be ordered:
a query can reference another one that is later in the list. (However,
circular references, or mutual recursion, are not implemented.)
Without RECURSIVE, WITH queries can only reference sibling WITH
queries that are earlier in the WITH list.

Bold emphasis mine again. Meaning that the order of WITH clauses is meaningless when the RECURSIVE key word has been used.

BTW, since cte1 and cte2 in the example are not referenced in the outer SELECT and are plain SELECT commands themselves (no collateral effects), they are never executed (unless referenced in cte3).

More Related Contents:

  1. How to use multiple CTEs in a single SQL query?
  2. Insert data in 3 tables at a time using Postgres
  3. PostgreSQL multi INSERT…RETURNING with multiple columns
  4. INSERT rows into multiple tables in a single query, selecting from an involved table
  5. Delete parent if it’s not referenced by any other child
  6. Window Functions or Common Table Expressions: count previous rows within range
  7. Select statement to return parent and infinite children
  8. Store and reuse value returned by INSERT … RETURNING
  9. Create a pivot table with PostgreSQL
  10. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  11. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  12. How to select id with max date group by category in PostgreSQL?
  13. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  14. CTE error: “Types don’t match between the anchor and the recursive part”
  15. Error: Column does not exist
  16. DROP FUNCTION without knowing the number/type of parameters?
  17. Multiple array_agg() calls in a single query
  18. Calculate working hours between 2 dates in PostgreSQL
  19. How to show row numbers in PostgreSQL query?
  20. Rails 4 LIKE query – ActiveRecord adds quotes
  21. PostgreSQL Index on JSON
  22. Operator does not exist: json = json
  23. Function with SQL query has no destination for result data
  24. Postgres INTERVAL using value from table
  25. Count consecutive duplicate values in SQL
  26. Crosstab with a large or undefined number of categories
  27. Truncate timestamp to arbitrary intervals
  28. Conditional lead/lag function PostgreSQL?
  29. Postgres and Indexes on Foreign Keys and Primary Keys
  30. Select random row from a PostgreSQL table with weighted row probabilities

Leave a Comment