codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>7.8. WITH Queries (Common Table Expressions)</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="queries-values.html" title="7.7. VALUES Lists" /><link rel="next" href="datatype.html" title="Chapter 8. Data Types" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">7.8. <code class="literal">WITH</code> Queries (Common Table Expressions)</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="queries-values.html" title="7.7. VALUES Lists">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><th width="60%" align="center">Chapter 7. Queries</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype.html" title="Chapter 8. Data Types">Next</a></td></tr></table><hr /></div><div class="sect1" id="QUERIES-WITH"><div class="titlepage"><div><div><h2 class="title" style="clear: both">7.8. <code class="literal">WITH</code> Queries (Common Table Expressions) <a href="#QUERIES-WITH" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-SELECT">7.8.1. <code class="command">SELECT</code> in <code class="literal">WITH</code></a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-RECURSIVE">7.8.2. Recursive Queries</a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION">7.8.3. Common Table Expression Materialization</a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-MODIFYING">7.8.4. Data-Modifying Statements in <code class="literal">WITH</code></a></span></dt></dl></div><a id="id-1.5.6.12.2" class="indexterm"></a><a id="id-1.5.6.12.3" class="indexterm"></a><p>3 <code class="literal">WITH</code> provides a way to write auxiliary statements for use in a4 larger query. These statements, which are often referred to as Common5 Table Expressions or <acronym class="acronym">CTE</acronym>s, can be thought of as defining6 temporary tables that exist just for one query. Each auxiliary statement7 in a <code class="literal">WITH</code> clause can be a <code class="command">SELECT</code>,8 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>; and the9 <code class="literal">WITH</code> clause itself is attached to a primary statement that can10 be a <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,11 <code class="command">DELETE</code>, or <code class="command">MERGE</code>.12 </p><div class="sect2" id="QUERIES-WITH-SELECT"><div class="titlepage"><div><div><h3 class="title">7.8.1. <code class="command">SELECT</code> in <code class="literal">WITH</code> <a href="#QUERIES-WITH-SELECT" class="id_link">#</a></h3></div></div></div><p>13 The basic value of <code class="command">SELECT</code> in <code class="literal">WITH</code> is to14 break down complicated queries into simpler parts. An example is:15 16</p><pre class="programlisting">17WITH regional_sales AS (18 SELECT region, SUM(amount) AS total_sales19 FROM orders20 GROUP BY region21), top_regions AS (22 SELECT region23 FROM regional_sales24 WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)25)26SELECT region,27 product,28 SUM(quantity) AS product_units,29 SUM(amount) AS product_sales30FROM orders31WHERE region IN (SELECT region FROM top_regions)32GROUP BY region, product;33</pre><p>34 35 which displays per-product sales totals in only the top sales regions.36 The <code class="literal">WITH</code> clause defines two auxiliary statements named37 <code class="structname">regional_sales</code> and <code class="structname">top_regions</code>,38 where the output of <code class="structname">regional_sales</code> is used in39 <code class="structname">top_regions</code> and the output of <code class="structname">top_regions</code>40 is used in the primary <code class="command">SELECT</code> query.41 This example could have been written without <code class="literal">WITH</code>,42 but we'd have needed two levels of nested sub-<code class="command">SELECT</code>s. It's a bit43 easier to follow this way.44 </p></div><div class="sect2" id="QUERIES-WITH-RECURSIVE"><div class="titlepage"><div><div><h3 class="title">7.8.2. Recursive Queries <a href="#QUERIES-WITH-RECURSIVE" class="id_link">#</a></h3></div></div></div><p>45 <a id="id-1.5.6.12.6.2.1" class="indexterm"></a>46 The optional <code class="literal">RECURSIVE</code> modifier changes <code class="literal">WITH</code>47 from a mere syntactic convenience into a feature that accomplishes48 things not otherwise possible in standard SQL. Using49 <code class="literal">RECURSIVE</code>, a <code class="literal">WITH</code> query can refer to its own50 output. A very simple example is this query to sum the integers from 151 through 100:52 53</p><pre class="programlisting">54WITH RECURSIVE t(n) AS (55 VALUES (1)56 UNION ALL57 SELECT n+1 FROM t WHERE n < 10058)59SELECT sum(n) FROM t;60</pre><p>61 62 The general form of a recursive <code class="literal">WITH</code> query is always a63 <em class="firstterm">non-recursive term</em>, then <code class="literal">UNION</code> (or64 <code class="literal">UNION ALL</code>), then a65 <em class="firstterm">recursive term</em>, where only the recursive term can contain66 a reference to the query's own output. Such a query is executed as67 follows:68 </p><div class="procedure" id="id-1.5.6.12.6.3"><p class="title"><strong>Recursive Query Evaluation</strong></p><ol class="procedure" type="1"><li class="step"><p>69 Evaluate the non-recursive term. For <code class="literal">UNION</code> (but not70 <code class="literal">UNION ALL</code>), discard duplicate rows. Include all remaining71 rows in the result of the recursive query, and also place them in a72 temporary <em class="firstterm">working table</em>.73 </p></li><li class="step"><p>74 So long as the working table is not empty, repeat these steps:75 </p><ol type="a" class="substeps"><li class="step"><p>76 Evaluate the recursive term, substituting the current contents of77 the working table for the recursive self-reference.78 For <code class="literal">UNION</code> (but not <code class="literal">UNION ALL</code>), discard79 duplicate rows and rows that duplicate any previous result row.80 Include all remaining rows in the result of the recursive query, and81 also place them in a temporary <em class="firstterm">intermediate table</em>.82 </p></li><li class="step"><p>83 Replace the contents of the working table with the contents of the84 intermediate table, then empty the intermediate table.85 </p></li></ol></li></ol></div><div class="note"><h3 class="title">Note</h3><p>86 While <code class="literal">RECURSIVE</code> allows queries to be specified87 recursively, internally such queries are evaluated iteratively.88 </p></div><p>89 In the example above, the working table has just a single row in each step,90 and it takes on the values from 1 through 100 in successive steps. In91 the 100th step, there is no output because of the <code class="literal">WHERE</code>92 clause, and so the query terminates.93 </p><p>94 Recursive queries are typically used to deal with hierarchical or95 tree-structured data. A useful example is this query to find all the96 direct and indirect sub-parts of a product, given only a table that97 shows immediate inclusions:98 99</p><pre class="programlisting">100WITH RECURSIVE included_parts(sub_part, part, quantity) AS (101 SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'102 UNION ALL103 SELECT p.sub_part, p.part, p.quantity * pr.quantity104 FROM included_parts pr, parts p105 WHERE p.part = pr.sub_part106)107SELECT sub_part, SUM(quantity) as total_quantity108FROM included_parts109GROUP BY sub_part110</pre><p>111 </p><div class="sect3" id="QUERIES-WITH-SEARCH"><div class="titlepage"><div><div><h4 class="title">7.8.2.1. Search Order <a href="#QUERIES-WITH-SEARCH" class="id_link">#</a></h4></div></div></div><p>112 When computing a tree traversal using a recursive query, you might want to113 order the results in either depth-first or breadth-first order. This can114 be done by computing an ordering column alongside the other data columns115 and using that to sort the results at the end. Note that this does not116 actually control in which order the query evaluation visits the rows; that117 is as always in SQL implementation-dependent. This approach merely118 provides a convenient way to order the results afterwards.119 </p><p>120 To create a depth-first order, we compute for each result row an array of121 rows that we have visited so far. For example, consider the following122 query that searches a table <code class="structname">tree</code> using a123 <code class="structfield">link</code> field:124 125</p><pre class="programlisting">126WITH RECURSIVE search_tree(id, link, data) AS (127 SELECT t.id, t.link, t.data128 FROM tree t129 UNION ALL130 SELECT t.id, t.link, t.data131 FROM tree t, search_tree st132 WHERE t.id = st.link133)134SELECT * FROM search_tree;135</pre><p>136 137 To add depth-first ordering information, you can write this:138 139</p><pre class="programlisting">140WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (141 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[t.id]</strong></span>142 FROM tree t143 UNION ALL144 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || t.id</strong></span>145 FROM tree t, search_tree st146 WHERE t.id = st.link147)148SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;149</pre><p>150 </p><p>151 In the general case where more than one field needs to be used to identify152 a row, use an array of rows. For example, if we needed to track fields153 <code class="structfield">f1</code> and <code class="structfield">f2</code>:154 155</p><pre class="programlisting">156WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (157 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[ROW(t.f1, t.f2)]</strong></span>158 FROM tree t159 UNION ALL160 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || ROW(t.f1, t.f2)</strong></span>161 FROM tree t, search_tree st162 WHERE t.id = st.link163)164SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;165</pre><p>166 </p><div class="tip"><h3 class="title">Tip</h3><p>167 Omit the <code class="literal">ROW()</code> syntax in the common case where only one168 field needs to be tracked. This allows a simple array rather than a169 composite-type array to be used, gaining efficiency.170 </p></div><p>171 To create a breadth-first order, you can add a column that tracks the depth172 of the search, for example:173 174</p><pre class="programlisting">175WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>depth</strong></span>) AS (176 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>0</strong></span>177 FROM tree t178 UNION ALL179 SELECT t.id, t.link, t.data, <span class="emphasis"><strong>depth + 1</strong></span>180 FROM tree t, search_tree st181 WHERE t.id = st.link182)183SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY depth</strong></span>;184</pre><p>185 186 To get a stable sort, add data columns as secondary sorting columns.187 </p><div class="tip"><h3 class="title">Tip</h3><p>188 The recursive query evaluation algorithm produces its output in189 breadth-first search order. However, this is an implementation detail and190 it is perhaps unsound to rely on it. The order of the rows within each191 level is certainly undefined, so some explicit ordering might be desired192 in any case.193 </p></div><p>194 There is built-in syntax to compute a depth- or breadth-first sort column.195 For example:196 197</p><pre class="programlisting">198WITH RECURSIVE search_tree(id, link, data) AS (199 SELECT t.id, t.link, t.data200 FROM tree t201 UNION ALL202 SELECT t.id, t.link, t.data203 FROM tree t, search_tree st204 WHERE t.id = st.link205) <span class="emphasis"><strong>SEARCH DEPTH FIRST BY id SET ordercol</strong></span>206SELECT * FROM search_tree ORDER BY ordercol;207 208WITH RECURSIVE search_tree(id, link, data) AS (209 SELECT t.id, t.link, t.data210 FROM tree t211 UNION ALL212 SELECT t.id, t.link, t.data213 FROM tree t, search_tree st214 WHERE t.id = st.link215) <span class="emphasis"><strong>SEARCH BREADTH FIRST BY id SET ordercol</strong></span>216SELECT * FROM search_tree ORDER BY ordercol;217</pre><p>218 This syntax is internally expanded to something similar to the above219 hand-written forms. The <code class="literal">SEARCH</code> clause specifies whether220 depth- or breadth first search is wanted, the list of columns to track for221 sorting, and a column name that will contain the result data that can be222 used for sorting. That column will implicitly be added to the output rows223 of the CTE.224 </p></div><div class="sect3" id="QUERIES-WITH-CYCLE"><div class="titlepage"><div><div><h4 class="title">7.8.2.2. Cycle Detection <a href="#QUERIES-WITH-CYCLE" class="id_link">#</a></h4></div></div></div><p>225 When working with recursive queries it is important to be sure that226 the recursive part of the query will eventually return no tuples,227 or else the query will loop indefinitely. Sometimes, using228 <code class="literal">UNION</code> instead of <code class="literal">UNION ALL</code> can accomplish this229 by discarding rows that duplicate previous output rows. However, often a230 cycle does not involve output rows that are completely duplicate: it may be231 necessary to check just one or a few fields to see if the same point has232 been reached before. The standard method for handling such situations is233 to compute an array of the already-visited values. For example, consider again234 the following query that searches a table <code class="structname">graph</code> using a235 <code class="structfield">link</code> field:236 237</p><pre class="programlisting">238WITH RECURSIVE search_graph(id, link, data, depth) AS (239 SELECT g.id, g.link, g.data, 0240 FROM graph g241 UNION ALL242 SELECT g.id, g.link, g.data, sg.depth + 1243 FROM graph g, search_graph sg244 WHERE g.id = sg.link245)246SELECT * FROM search_graph;247</pre><p>248 249 This query will loop if the <code class="structfield">link</code> relationships contain250 cycles. Because we require a <span class="quote">“<span class="quote">depth</span>”</span> output, just changing251 <code class="literal">UNION ALL</code> to <code class="literal">UNION</code> would not eliminate the looping.252 Instead we need to recognize whether we have reached the same row again253 while following a particular path of links. We add two columns254 <code class="structfield">is_cycle</code> and <code class="structfield">path</code> to the loop-prone query:255 256</p><pre class="programlisting">257WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (258 SELECT g.id, g.link, g.data, 0,259 <span class="emphasis"><strong>false,260 ARRAY[g.id]</strong></span>261 FROM graph g262 UNION ALL263 SELECT g.id, g.link, g.data, sg.depth + 1,264 <span class="emphasis"><strong>g.id = ANY(path),265 path || g.id</strong></span>266 FROM graph g, search_graph sg267 WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>268)269SELECT * FROM search_graph;270</pre><p>271 272 Aside from preventing cycles, the array value is often useful in its own273 right as representing the <span class="quote">“<span class="quote">path</span>”</span> taken to reach any particular row.274 </p><p>275 In the general case where more than one field needs to be checked to276 recognize a cycle, use an array of rows. For example, if we needed to277 compare fields <code class="structfield">f1</code> and <code class="structfield">f2</code>:278 279</p><pre class="programlisting">280WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (281 SELECT g.id, g.link, g.data, 0,282 <span class="emphasis"><strong>false,283 ARRAY[ROW(g.f1, g.f2)]</strong></span>284 FROM graph g285 UNION ALL286 SELECT g.id, g.link, g.data, sg.depth + 1,287 <span class="emphasis"><strong>ROW(g.f1, g.f2) = ANY(path),288 path || ROW(g.f1, g.f2)</strong></span>289 FROM graph g, search_graph sg290 WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>291)292SELECT * FROM search_graph;293</pre><p>294 </p><div class="tip"><h3 class="title">Tip</h3><p>295 Omit the <code class="literal">ROW()</code> syntax in the common case where only one field296 needs to be checked to recognize a cycle. This allows a simple array297 rather than a composite-type array to be used, gaining efficiency.298 </p></div><p>299 There is built-in syntax to simplify cycle detection. The above query can300 also be written like this:301</p><pre class="programlisting">302WITH RECURSIVE search_graph(id, link, data, depth) AS (303 SELECT g.id, g.link, g.data, 1304 FROM graph g305 UNION ALL306 SELECT g.id, g.link, g.data, sg.depth + 1307 FROM graph g, search_graph sg308 WHERE g.id = sg.link309) <span class="emphasis"><strong>CYCLE id SET is_cycle USING path</strong></span>310SELECT * FROM search_graph;311</pre><p>312 and it will be internally rewritten to the above form. The313 <code class="literal">CYCLE</code> clause specifies first the list of columns to314 track for cycle detection, then a column name that will show whether a315 cycle has been detected, and finally the name of another column that will track the316 path. The cycle and path columns will implicitly be added to the output317 rows of the CTE.318 </p><div class="tip"><h3 class="title">Tip</h3><p>319 The cycle path column is computed in the same way as the depth-first320 ordering column show in the previous section. A query can have both a321 <code class="literal">SEARCH</code> and a <code class="literal">CYCLE</code> clause, but a322 depth-first search specification and a cycle detection specification would323 create redundant computations, so it's more efficient to just use the324 <code class="literal">CYCLE</code> clause and order by the path column. If325 breadth-first ordering is wanted, then specifying both326 <code class="literal">SEARCH</code> and <code class="literal">CYCLE</code> can be useful.327 </p></div><p>328 A helpful trick for testing queries329 when you are not certain if they might loop is to place a <code class="literal">LIMIT</code>330 in the parent query. For example, this query would loop forever without331 the <code class="literal">LIMIT</code>:332 333</p><pre class="programlisting">334WITH RECURSIVE t(n) AS (335 SELECT 1336 UNION ALL337 SELECT n+1 FROM t338)339SELECT n FROM t <span class="emphasis"><strong>LIMIT 100</strong></span>;340</pre><p>341 342 This works because <span class="productname">PostgreSQL</span>'s implementation343 evaluates only as many rows of a <code class="literal">WITH</code> query as are actually344 fetched by the parent query. Using this trick in production is not345 recommended, because other systems might work differently. Also, it346 usually won't work if you make the outer query sort the recursive query's347 results or join them to some other table, because in such cases the348 outer query will usually try to fetch all of the <code class="literal">WITH</code> query's349 output anyway.350 </p></div></div><div class="sect2" id="QUERIES-WITH-CTE-MATERIALIZATION"><div class="titlepage"><div><div><h3 class="title">7.8.3. Common Table Expression Materialization <a href="#QUERIES-WITH-CTE-MATERIALIZATION" class="id_link">#</a></h3></div></div></div><p>351 A useful property of <code class="literal">WITH</code> queries is that they are352 normally evaluated only once per execution of the parent query, even if353 they are referred to more than once by the parent query or354 sibling <code class="literal">WITH</code> queries.355 Thus, expensive calculations that are needed in multiple places can be356 placed within a <code class="literal">WITH</code> query to avoid redundant work. Another357 possible application is to prevent unwanted multiple evaluations of358 functions with side-effects.359 However, the other side of this coin is that the optimizer is not able to360 push restrictions from the parent query down into a multiply-referenced361 <code class="literal">WITH</code> query, since that might affect all uses of the362 <code class="literal">WITH</code> query's output when it should affect only one.363 The multiply-referenced <code class="literal">WITH</code> query will be364 evaluated as written, without suppression of rows that the parent query365 might discard afterwards. (But, as mentioned above, evaluation might stop366 early if the reference(s) to the query demand only a limited number of367 rows.)368 </p><p>369 However, if a <code class="literal">WITH</code> query is non-recursive and370 side-effect-free (that is, it is a <code class="literal">SELECT</code> containing371 no volatile functions) then it can be folded into the parent query,372 allowing joint optimization of the two query levels. By default, this373 happens if the parent query references the <code class="literal">WITH</code> query374 just once, but not if it references the <code class="literal">WITH</code> query375 more than once. You can override that decision by376 specifying <code class="literal">MATERIALIZED</code> to force separate calculation377 of the <code class="literal">WITH</code> query, or by specifying <code class="literal">NOT378 MATERIALIZED</code> to force it to be merged into the parent query.379 The latter choice risks duplicate computation of380 the <code class="literal">WITH</code> query, but it can still give a net savings if381 each usage of the <code class="literal">WITH</code> query needs only a small part382 of the <code class="literal">WITH</code> query's full output.383 </p><p>384 A simple example of these rules is385</p><pre class="programlisting">386WITH w AS (387 SELECT * FROM big_table388)389SELECT * FROM w WHERE key = 123;390</pre><p>391 This <code class="literal">WITH</code> query will be folded, producing the same392 execution plan as393</p><pre class="programlisting">394SELECT * FROM big_table WHERE key = 123;395</pre><p>396 In particular, if there's an index on <code class="structfield">key</code>,397 it will probably be used to fetch just the rows having <code class="literal">key =398 123</code>. On the other hand, in399</p><pre class="programlisting">400WITH w AS (401 SELECT * FROM big_table402)403SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref404WHERE w2.key = 123;405</pre><p>406 the <code class="literal">WITH</code> query will be materialized, producing a407 temporary copy of <code class="structname">big_table</code> that is then408 joined with itself — without benefit of any index. This query409 will be executed much more efficiently if written as410</p><pre class="programlisting">411WITH w AS NOT MATERIALIZED (412 SELECT * FROM big_table413)414SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref415WHERE w2.key = 123;416</pre><p>417 so that the parent query's restrictions can be applied directly418 to scans of <code class="structname">big_table</code>.419 </p><p>420 An example where <code class="literal">NOT MATERIALIZED</code> could be421 undesirable is422</p><pre class="programlisting">423WITH w AS (424 SELECT key, very_expensive_function(val) as f FROM some_table425)426SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;427</pre><p>428 Here, materialization of the <code class="literal">WITH</code> query ensures429 that <code class="function">very_expensive_function</code> is evaluated only430 once per table row, not twice.431 </p><p>432 The examples above only show <code class="literal">WITH</code> being used with433 <code class="command">SELECT</code>, but it can be attached in the same way to434 <code class="command">INSERT</code>, <code class="command">UPDATE</code>,435 <code class="command">DELETE</code>, or <code class="command">MERGE</code>.436 In each case it effectively provides temporary table(s) that can437 be referred to in the main command.438 </p></div><div class="sect2" id="QUERIES-WITH-MODIFYING"><div class="titlepage"><div><div><h3 class="title">7.8.4. Data-Modifying Statements in <code class="literal">WITH</code> <a href="#QUERIES-WITH-MODIFYING" class="id_link">#</a></h3></div></div></div><p>439 You can use most data-modifying statements (<code class="command">INSERT</code>,440 <code class="command">UPDATE</code>, or <code class="command">DELETE</code>, but not441 <code class="command">MERGE</code>) in <code class="literal">WITH</code>. This442 allows you to perform several different operations in the same query.443 An example is:444 445</p><pre class="programlisting">446WITH moved_rows AS (447 DELETE FROM products448 WHERE449 "date" >= '2010-10-01' AND450 "date" < '2010-11-01'451 RETURNING *452)453INSERT INTO products_log454SELECT * FROM moved_rows;455</pre><p>456 457 This query effectively moves rows from <code class="structname">products</code> to458 <code class="structname">products_log</code>. The <code class="command">DELETE</code> in <code class="literal">WITH</code>459 deletes the specified rows from <code class="structname">products</code>, returning their460 contents by means of its <code class="literal">RETURNING</code> clause; and then the461 primary query reads that output and inserts it into462 <code class="structname">products_log</code>.463 </p><p>464 A fine point of the above example is that the <code class="literal">WITH</code> clause is465 attached to the <code class="command">INSERT</code>, not the sub-<code class="command">SELECT</code> within466 the <code class="command">INSERT</code>. This is necessary because data-modifying467 statements are only allowed in <code class="literal">WITH</code> clauses that are attached468 to the top-level statement. However, normal <code class="literal">WITH</code> visibility469 rules apply, so it is possible to refer to the <code class="literal">WITH</code>470 statement's output from the sub-<code class="command">SELECT</code>.471 </p><p>472 Data-modifying statements in <code class="literal">WITH</code> usually have473 <code class="literal">RETURNING</code> clauses (see <a class="xref" href="dml-returning.html" title="6.4. Returning Data from Modified Rows">Section 6.4</a>),474 as shown in the example above.475 It is the output of the <code class="literal">RETURNING</code> clause, <span class="emphasis"><em>not</em></span> the476 target table of the data-modifying statement, that forms the temporary477 table that can be referred to by the rest of the query. If a478 data-modifying statement in <code class="literal">WITH</code> lacks a <code class="literal">RETURNING</code>479 clause, then it forms no temporary table and cannot be referred to in480 the rest of the query. Such a statement will be executed nonetheless.481 A not-particularly-useful example is:482 483</p><pre class="programlisting">484WITH t AS (485 DELETE FROM foo486)487DELETE FROM bar;488</pre><p>489 490 This example would remove all rows from tables <code class="structname">foo</code> and491 <code class="structname">bar</code>. The number of affected rows reported to the client492 would only include rows removed from <code class="structname">bar</code>.493 </p><p>494 Recursive self-references in data-modifying statements are not495 allowed. In some cases it is possible to work around this limitation by496 referring to the output of a recursive <code class="literal">WITH</code>, for example:497 498</p><pre class="programlisting">499WITH RECURSIVE included_parts(sub_part, part) AS (500 SELECT sub_part, part FROM parts WHERE part = 'our_product'501 UNION ALL502 SELECT p.sub_part, p.part503 FROM included_parts pr, parts p504 WHERE p.part = pr.sub_part505)506DELETE FROM parts507 WHERE part IN (SELECT part FROM included_parts);508</pre><p>509 510 This query would remove all direct and indirect subparts of a product.511 </p><p>512 Data-modifying statements in <code class="literal">WITH</code> are executed exactly once,513 and always to completion, independently of whether the primary query514 reads all (or indeed any) of their output. Notice that this is different515 from the rule for <code class="command">SELECT</code> in <code class="literal">WITH</code>: as stated in the516 previous section, execution of a <code class="command">SELECT</code> is carried only as far517 as the primary query demands its output.518 </p><p>519 The sub-statements in <code class="literal">WITH</code> are executed concurrently with520 each other and with the main query. Therefore, when using data-modifying521 statements in <code class="literal">WITH</code>, the order in which the specified updates522 actually happen is unpredictable. All the statements are executed with523 the same <em class="firstterm">snapshot</em> (see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>), so they524 cannot <span class="quote">“<span class="quote">see</span>”</span> one another's effects on the target tables. This525 alleviates the effects of the unpredictability of the actual order of row526 updates, and means that <code class="literal">RETURNING</code> data is the only way to527 communicate changes between different <code class="literal">WITH</code> sub-statements and528 the main query. An example of this is that in529 530</p><pre class="programlisting">531WITH t AS (532 UPDATE products SET price = price * 1.05533 RETURNING *534)535SELECT * FROM products;536</pre><p>537 538 the outer <code class="command">SELECT</code> would return the original prices before the539 action of the <code class="command">UPDATE</code>, while in540 541</p><pre class="programlisting">542WITH t AS (543 UPDATE products SET price = price * 1.05544 RETURNING *545)546SELECT * FROM t;547</pre><p>548 549 the outer <code class="command">SELECT</code> would return the updated data.550 </p><p>551 Trying to update the same row twice in a single statement is not552 supported. Only one of the modifications takes place, but it is not easy553 (and sometimes not possible) to reliably predict which one. This also554 applies to deleting a row that was already updated in the same statement:555 only the update is performed. Therefore you should generally avoid trying556 to modify a single row twice in a single statement. In particular avoid557 writing <code class="literal">WITH</code> sub-statements that could affect the same rows558 changed by the main statement or a sibling sub-statement. The effects559 of such a statement will not be predictable.560 </p><p>561 At present, any table used as the target of a data-modifying statement in562 <code class="literal">WITH</code> must not have a conditional rule, nor an <code class="literal">ALSO</code>563 rule, nor an <code class="literal">INSTEAD</code> rule that expands to multiple statements.564 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="queries-values.html" title="7.7. VALUES Lists">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype.html" title="Chapter 8. Data Types">Next</a></td></tr><tr><td width="40%" align="left" valign="top">7.7. <code class="literal">VALUES</code> Lists </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 8. Data Types</td></tr></table></div></body></html>