Common table Expression(CTE) – PostgreSQL and Oracle

    A common table expression(CTE) is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE.

    Common Table Expressions(CTE) are temporary in the sense that they only exist during the execution of the query.

    WITH cte_name (column_list) AS (
    • First, specify the name of the CTE following by an optional column list.
    • Second, inside the body of the WITH clause, specify a query that returns a result set.
    • If you do not explicitly specify the column list after the CTE name, the select list of the CTE_query_definition will become the column list of the CTE.
    • Third, use the CTE like a table or view in the statement which can be a SELECT, INSERT, UPDATE, or DELETE.
    • Common Table Expressions or CTEs are typically used to simplify complex joins and subqueries in PostgreSQL.

    A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects.


    As an example , I will show one select cte for table “film” of the sample database(dvdrental). Description of the table and few rows are shown in the below image.

    CTE query

    WITH cte_film AS (
    WHEN length < 30 THEN 'Short'
    WHEN length < 90 THEN 'Medium'
    ELSE 'Long'
    END) length
    FROM film
    )SELECT film_id,title,length FROM cte_film WHERE length = 'Long' ORDER BY title;

    Example 2:

    Example 1 was a very simple example with selecting data only from the cte table. Here I will show you little more complex query which will join data from staff table of sample database dvdrental after writing cte query on table rental.

    WITH cte_rental AS (
    SELECT staff_id,
    COUNT(rental_id) rental_count
    FROM rental
    GROUP BY staff_id
    SELECT s.staff_id,
    FROM staff s INNER JOIN cte_rental USING (staff_id);

    Example 3

    Little more complex queries.

    WITH regional_sales AS (
            SELECT region, SUM(amount) AS total_sales
            FROM orders
            GROUP BY region
         ), top_regions AS (
            SELECT region
            FROM regional_sales
            WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
    SELECT region,
           SUM(quantity) AS product_units,
           SUM(amount) AS product_sales
    FROM orders
    WHERE region IN (SELECT region FROM top_regions)
    GROUP BY region, product;


    Recursive queries are typically used to deal with hierarchical or tree-structured data. In the following example, I have shown you how to do a simple recursive call for 1 to 100 numbers. You can extend this same query for multiple complex queries.

        SELECT 1
        SELECT n+1 FROM t
    SELECT n FROM t LIMIT 100;
    numbers till 100 is there in the output . I have just shown a snippet of it.


    We can also delete from some table and then insert those data into some other table using CTE. A simple example is shown below.In the following example it will delete details of the product from the production table which were created between 12th Jan,2021 and 20th Jan,20201 and insert those same details into product_log table as an archived data.

    WITH moved_rows AS (
        DELETE FROM products
            "date" >= '2021-01-12' AND
            "date" < '2021-01-20'
        RETURNING *
    INSERT INTO products_log SELECT * FROM moved_rows;


    We can do a recursive delete as well as we did in SELECT statement. Following is example for the same.

    WITH RECURSIVE included_parts(sub_part, part) AS (
        SELECT sub_part, part FROM parts WHERE part = 'our_product'
        SELECT p.sub_part, p.part
        FROM included_parts pr, parts p
        WHERE p.part = pr.sub_part
    DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);


    We can also update table within CTE statement. A simple example is shown here.

    WITH t AS (
        UPDATE payment SET amount = amount * 1.05
        RETURNING *
    )SELECT * FROM payment limit 5;

     payment_id | customer_id | staff_id | rental_id | amount |        payment_date        
          17503 |         341 |        2 |      1520 |   7.99 | 2007-02-15 22:25:46.996577
          17504 |         341 |        1 |      1778 |   1.99 | 2007-02-16 17:23:14.996577
          17505 |         341 |        1 |      1849 |   7.99 | 2007-02-16 22:41:45.996577
          17506 |         341 |        2 |      2829 |   2.99 | 2007-02-19 19:39:56.996577
          17507 |         341 |        2 |      3130 |   7.99 | 2007-02-20 17:31:48.996577
    (5 rows)
    dvdrental=# WITH t AS (
        UPDATE payment SET amount = amount * 1.05 where payment_id='17503'
        RETURNING *
    ) SELECT * FROM payment where payment_id='17503';
    payment_id | customer_id | staff_id | rental_id | amount |        payment_date      
          17503 |         341 |        2 |      1520 |   8.39 | 2007-02-15 22:25:46.996577
    (1 row)

    Important Facts about CTE in PostgreSQL.

    • Using CTE we can “cache” expensive calculations that are reused multiple times in the query, but the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary subquery.
    • PostgreSQL materializes the CTE, meaning, it creates a temporary structure with the results of the query defined in the CTE, and only then apply the filter to it. Because the predicate is not applied on the table (but the CTE) PostgreSQL was unable to utilize the index on the ID column. In those cases we can use subquery instead of CTE. In case of CTE, we might get a different query plan than SUBQUERY  which is not the case with ORACLE CTE as it does not materializes the CTE output.

    Recent Articles


    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox