WITH clause provides a way to use subqueries or perform a data modifying operation in a larger
SELECT query. You can also use the
WITH clause in an
See SELECT in a WITH Clause for information about using
SELECT in a
See Data-Modifying Statements in a WITH clause, for information about using
DELETE in a
These are limitations for using a
SELECTcommand that includes a
WITHclause, the clause can contain at most a single clause that modifies table data (
DELETE) that includes a
WITHclause, the clause can only contain a
WITHclause cannot contain a data-modifying command.
By default, the
RECURSIVE keyword for the
WITH clause is enabled.
RECURSIVE can be deactivated by setting the server configuration parameter gp_recursive_cte to
Parent topic: Querying Data
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
DELETE. In each case, the
WITH clause effectively provides temporary tables that can be referred to in the main command.
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:
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, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
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
When the optional
RECURSIVE keyword is enabled, the
WITH clause can accomplish things not otherwise possible in standard SQL. Using
RECURSIVE, a query in the
WITH clause can refer to its own output. This is a simple example that computes the sum of integers from 1 through 100:
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
The general form of a recursive
WITH clause (a
WITH clause that uses a the
RECURSIVE keyword) is a non-recursive term, followed by a
UNION ALL), and then a recursive term, where only the recursive term can contain a reference to the query output.
<non_recursive_term> UNION [ ALL ] <recursive_term>
WITH query that contains a
UNION [ ALL ] is run as follows:
UNION ALL), discard duplicate rows. Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.
UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.
Strictly speaking, the process is iteration not recursion, but
RECURSIVEis the terminology chosen by the SQL standards committee.
WITH queries are typically used to deal with hierarchical or tree-structured data. An example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part ;
When working with recursive
WITH queries, you must ensure that the recursive part of the query eventually returns no tuples, or else the query loops indefinitely. In the example that computes the sum of integers, the working table contains a single row in each step, and it takes on the values from 1 through 100 in successive steps. In the 100th step, there is no output because of the
WHERE clause, and the query terminates.
For some queries, using
UNION instead of
UNION ALL can ensure that the recursive part of the query eventually returns no tuples by discarding rows that duplicate previous output rows. However, often a cycle does not involve output rows that are complete duplicates: it might be sufficient to check just one or a few fields to see if the same point has been reached before. The standard method for handling such situations is to compute an array of the visited values. For example, consider the following query that searches a table graph using a link field:
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;
This query loops if the link relationships contain cycles. Because the query requires a
depth output, changing
UNION ALL to
UNION does not eliminate the looping. Instead the query needs to recognize whether it has reached the same row again while following a particular path of links. This modified query adds two columns,
cycle, to the loop-prone query:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
Aside from detecting cycles, the array value of
path is useful in its own right since it represents the path taken to reach any particular row.
In the general case where more than one field needs to be checked to recognize a cycle, an array of rows can be used. For example, if we needed to compare fields
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[ROW(g.f1, g.f2)], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
Tip: Omit the
ROW() syntax in the case where only one field needs to be checked to recognize a cycle. This uses a simple array rather than a composite-type array, gaining efficiency.
Tip: The recursive query evaluation algorithm produces its output in breadth-first search order. You can display the results in depth-first search order by making the outer query
ORDER BY a path column constructed in this way.
A helpful technique for testing a query when you are not certain if it might loop indefinitely is to place a
LIMIT in the parent query. For example, this query would loop forever without the
WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100;
The technique works because the recursive
WITH implementation evaluates only as many rows of a
WITH query as are actually fetched by the parent query. Using this technique in production is not recommended, because other systems might work differently. Also, the technique might not work if the outer query sorts the recursive
WITH results or join the results to another table.
SELECT command, you can use the data-modifying commands
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 run 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
DELETE in the
WITH clause deletes the specified rows from products, returning their contents by means of its
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.
If the optional
RECURSIVE keyword is enabled, recursive self-references in data-modifying statements are not allowed. In some cases it is possible to work around this limitation by referring to the output of a recursive
WITH. For example, this query would remove all direct and indirect subparts of a product.
WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL 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);
The sub-statements in a
WITH clause are run concurrently with each other and with the main query. Therefore, when using a data-modifying statement in a
WITH, the statement is run in a snapshot. The effects of the statement are not visible on the target tables. The
RETURNING data is the only way to communicate changes between different
WITH sub-statements and the main query. In this example, the outer
SELECT returns the original prices before the action of the
UPDATE in the
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;
In this example the outer
SELECT returns the updated data.
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;
Updating the same row twice in a single statement is not supported. The effects of such a statement will not be predictable. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to predict which modification occurs.
Any table used as the target of a data-modifying statement in a
WITH clause must not have a conditional rule, or an
ALSO rule, or an
INSTEAD rule that expands to multiple statements.