Clause
WITH
The WITH clause allows you to specify one or more subqueries that can be referenced by name in the main query. Subqueries effectively act as temporary tables or views for the duration of the main query. Each subquery can be a SELECT, TABLE, VALUES, INSERT, UPDATE, or DELETE statement. When writing a data modifier statement (INSERT, UPDATE, or DELETE) to WITH, it is common to include a RETURN clause. It is the result of RETURNING, not the underlying table that modifies the statement, which forms the temporary table that reads the main query. If RETURN is omitted, the statement continues to execute, but produces no results, so the main query cannot reference it as a table.
A name (no schema qualification) must be specified for each WITH query. Optionally, you can specify a list of column names; If omitted, the column names are inferred from the subquery.
If RECURSIVE is specified, allows a SELECT subquery to refer to itself by name. Such sub-consultation must be in the form
non_recursive_term UNION [ ALL | DIFFERENT ] recursive_term where the
recursive self-reference should appear on the right side of the UNION. Only one recursive self-reference per query is allowed. Recursive data modification statements are not supported, but you can use the results of a recursive SELECT query in a data modification statement. See Section 7.8 for an example.
Another effect of RECURSIVE is that you don’t need to sort WITH queries: a query can reference another that is further down the list. (However, circular references, or mutual recursion, are not implemented.) Without RECURSIVE, WITH queries can only reference peered WITH queries that are previously in the WITH list.
When there are multiple queries in the WITH clause, RECURSIVE must be written only once, immediately after WITH. It applies to all queries in the WITH clause, although it has no effect on queries that do not use recursion or forwarding references.
The optional SEARCH clause calculates a search sequence column that can be used to sort the results of a recursive query in order of breadth first or depth first. The list of column names provided specifies the row key to use to track visited rows. A column named search_seq_col_name is added to the list of results columns in the WITH query. This column can be sorted by in the outpatient clinic to achieve the respective order. See Section 7.8.2.1 for examples.
The optional clause CYCLE is used to detect cycles in recursive queries. The list of column names provided specifies the row key to use to track visited rows. A column named cycle_mark_col_name is added to the list of results columns in the WITH query. This column will be set to cycle_mark_value when a cycle is detected, or to cycle_mark_default. In addition, recursive junction processing will stop when a cycle is detected. cycle_mark_value and cycle_mark_default must be constant and must be coercable to a common data type, and the data type must have an inequality operator. (The SQL standard requires them to be Boolean constants or strings, but PostgreSQL does not.) By default, TRUE and FALSE (Boolean type) are used. In addition, a column named cycle_path_col_name will be added to the list of results columns in the WITH query. This column is used internally to keep track of visited rows. See Section 7.8.2.2 for examples.
Both the SEARCH clause and the CYCLE clause are only valid for recursive WITH queries. The with_query must be a UNION (or UNION ALL) of two SELECT commands (or equivalent) (not nested UNIONs). If both clauses are used, the column added by the SEARCH clause appears before the columns added by the CYCLE clause.
The main query and the WITH queries run (theoretically) at the same time. This implies that the effects of a data modifier statement on WITH cannot be seen elsewhere in the query except by reading its RETURN output. If two of these data modifier statements attempt to modify the same row, the results are not specified.
A key property of WITH queries is that they are typically evaluated only once per execution of the main query, even if the main query references them more than once. In particular, data modifier statements are guaranteed to be executed once and only once, regardless of whether the main query reads all or part of its output.
However, a WITH query may be marked NOT MATERIALIZED to remove this warranty. In that case, the WITH query can be folded into the primary query as if it were a simple sub-SELECT in the FROM clause of the primary query. This results in duplicate calculations if the main query references that WITH query more than once; but if each of these uses requires only a few rows of the total output of the WITH QUERY, NOT MATERIALIZED can provide net savings by allowing queries to be optimized together. NOT MATERIALIZED is ignored if it is attached to a WITH query that is recursive or not free of side effects (i.e., it is not a simple SELECT that contains no volatile functions).
By default, a WITH query without side effects is folded in the main query if it is used exactly once in the FROM clause of the main query. This allows the joint optimization of the two query levels in situations where they should be semantically invisible. However, such folding can be avoided by marking the WITH query as MATERIALIZED. That could be useful, for example, if the WITH query is being used as an optimization fence to prevent the planner from choosing a bad plan. PostgreSQL versions prior to v12 never did such folding, so queries written for earlier versions could rely on WITH to act as an optimization fence.
See Section 7.8 for additional information.
