CTE (PostgreSQL)

A CTE, or a common table expression, is a temporary result taken from a SQL statement. This statement can contain SELECT, INSERT, UPDATE, or DELETE instructions. The lifetime of a CTE is equal to the lifetime of the query.

(Ferrari and Pirozzi 2023, 145)

CTEs in PostgreSQL 12+

Starting from PostgreSQL version 12, things have changed, and two new options have been introduced for the execution of a CTE, namely MATERIALIZED and NOT MATERIALIZED.

(Ferrari and Pirozzi 2023, 146)

Recursive CTEs

  WITH RECURSIVE fib AS (
    SELECT 
      0 as level,
      0 as n,
      1 as m
    UNION ALL
    SELECT
      level + 1 as level,
      m as n,
      (n + m) as m
    FROM fib WHERE n < 10
  )
  SELECT * FROM fib;
  -- Outputs:
   level | n  | m  
  -------+----+----
       0 |  0 |  1
       1 |  1 |  1
       2 |  1 |  2
       3 |  2 |  3
       4 |  3 |  5
       5 |  5 |  8
       6 |  8 | 13
       7 | 13 | 21

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.