WITH Queries (Common Table Expressions)

SELECT in a WITH Clause

The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for the query. These examples show the WITH clause being used with a SELECT command. The example WITH clauses can be used the same way with INSERT, UPDATE, or DELETE. In each case, the WITH clause effectively provides temporary tables that can be referred to in the main command.

A SELECT command in the WITH clause is evaluated only once per execution of the parent query, even if it is referred to more than once by the parent query or sibling WITH clauses. Thus, expensive calculations that are needed in multiple places can be placed within a WITH clause to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be evaluated as written, without suppression of rows that the parent query might discard afterwards. However, evaluation might stop early if the references to the query demand only a limited number of rows.

One use of this feature is to break down complicated queries into simpler parts. This example query displays per-product sales totals in only the top sales regions:

drop table if exists orders cascade;

create table orders (prodId serial,pname varchar(50),quantity smallint,amount numeric(16,2),region varchar(30));

insert into orders (pname ,quantity ,amount ,region ) values
('part1',5,150,'region1'),
('part5',10,1250,'region3'),
('part12',15,1500,'region2'),
('part3',2,120,'region1'),
('part1',15,450,'region2'),
('part1',20,3300,'region3'),
('part3',5,600,'region1'),
('part2',50,1000,'region3'),
('part5',6,750,'region1'),
('part1',500,3000,'region2'),
('part2',12,2400,'region2'),
('part12',8,880,'region1');

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)/5 FROM regional_sales)
)
SELECT pname,region,SUM(quantity) AS product_units,SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY pname,region order by pname,region;

The query could have been written without the WITH clause, but would have required two levels of nested sub-SELECTs. It is easier to follow with the WITH clause.

Data-Modifying Statements in a WITH clause

For a SELECT command, you can use the data-modifying commands INSERT, UPDATE, or DELETE in a WITH clause. This allows you to perform several different operations in the same query.

A data-modifying statement in a WITH clause is executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of the output. This is different from the rule when using SELECT in a WITH clause, the execution of a SELECT continues only as long as the primary query demands its output.

This simple CTE query deletes rows from products. The DELETE in the WITH clause deletes the specified rows from products, returning their contents by means of its RETURNING clause.

WITH deleted_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
SELECT * FROM deleted_rows;

Data-modifying statements in a WITH clause must have RETURNING clauses, as shown in the previous example. It is the output of the RETURNING clause, not the target table of the data-modifying statement, that forms the temporary table that can be referred to by the rest of the query. If a data-modifying statement in a WITH lacks a RETURNING clause, an error is returned.