CTE [Common Table Expression]Last updated: 2020-08-13

A common table expression (CTE) can be seen as a view that is only valid for a single query

Some advantages to using CTEs

The query becomes more readable.
You can reference the CTE several times in the query, and it will be calculated only once.
You can use data modifying statements in the CTE (typically with a RETURNING clause).

CTE (WITH clauses)

CTE could also be written as a subquery in FROM,

Note

Note that before v12, PostgreSQL always materialized CTEs. That means, the CTE was calculated independently from the containing query. From v12 on, CTEs can be “inlined” into the query, which provides further optimization potential.


WITH RECURSIVE fib AS (
	SELECT 1 AS n,
			1::bigint AS "fib",
			1::bigint AS "fib+1"
	UNION ALL
	SELECT n+1,
			"fib+1",
			"fib" + "fib+1"
	FROM fib
)
SELECT n, "fib" FROM fib
LIMIT 20;

RECURSIVE

Recursive queries are written using recursive CTEs, that are CTEs containing the RECURSIVE keyword


WITH RECURSIVE ctename AS (
	SELECT --- non-recursive branch, cannot reference "ctename"
	UNION [ALL]
	SELECT --recursive branch referencing "ctename" 
) SELECT ...
	FROM ctename ...

How recursive queries are processed

PostgreSQL internally uses a working table to process recursive CTEs. This processing is not really recursive, but rather iterative

First, the working table is initialized by executing the non-recursive branch of the CTE. The result of the CTE is also initialized with this result set. If the recursive CTE uses UNION rather than UNION ALL, duplicate rows are removed.

Then, PostgreSQL repeats the following until the working table is empty>

Evaluate the recursive branch of the CTE, replacing the reference to the CTE with the working table.
Add all resulting rows to the CTE result. If UNION is used to combine the branches, discard duplicate rows.
Replace the working table with all new rows from the previous step (excluding any removed duplicates).

Danger of an endless loop

If the iteration never ends, the query will just keep running until the result table becomes big enough to cause an error. There are two ways to deal with that

Often you can avoid infinite recursion by using UNION, which removes duplicate result rows (but of course requires extra processing effort).
Another way is to place a LIMIT clause on the query that uses the CTE, because PostgreSQL stops processing if the recursive CTE has calculated as many rows as are fetched by the parent query. Not that this technique is not portable to other standard compliant databases.

Example

Pagination with Single Query

Adding generated columns


WITH RECURSIVE tens (n) AS (
	SELECT 10
	UNION ALL
	SELECT n+10 FROM tens WHERE n+10<= 100
)
SELECT n FROM tens;
n
-----
	10
	20
	30
	40
	50
	60
	70
	80
	90
	100
(10 rows)


WITH RECURSIVE ctename AS (
      SELECT empno, ename,
             0 AS level
      FROM emp
      WHERE empno = 1
   UNION ALL
      SELECT emp.empno, emp.ename,
             ctename.level + 1
      FROM emp
         JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;

empno | ename | level
-------+-------+-------
  1 | Thirumal |     0
  2 | Jack     |     1
  3 | Smith    |     2

Get the path


WITH RECURSIVE ctename AS (
	SELECT empno, ename,
			ename AS path
	FROM emp
	WHERE empno = 1
	UNION ALL
	SELECT emp.empno, emp.ename,
			ctename.path || ' -> ' || emp.ename
	FROM emp
		JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
	
empno | ename |          path          
-------+-------+------------------------
1 | Thirumal | Thirumal
2 | Jack     | Thirumal -> Jack
3 | Smith    | Thirumal -> Jack -> Smith