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>MERGE</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="sql-lock.html" title="LOCK" /><link rel="next" href="sql-move.html" title="MOVE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">MERGE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-lock.html" title="LOCK">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</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="sql-move.html" title="MOVE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-MERGE"><div class="titlepage"></div><a id="id-1.9.3.156.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">MERGE</span></h2><p>MERGE — conditionally insert, update, or delete rows of a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3[ WITH <em class="replaceable"><code>with_query</code></em> [, ...] ]4MERGE INTO [ ONLY ] <em class="replaceable"><code>target_table_name</code></em> [ * ] [ [ AS ] <em class="replaceable"><code>target_alias</code></em> ]5USING <em class="replaceable"><code>data_source</code></em> ON <em class="replaceable"><code>join_condition</code></em>6<em class="replaceable"><code>when_clause</code></em> [...]7 8<span class="phrase">where <em class="replaceable"><code>data_source</code></em> is:</span>9 10{ [ ONLY ] <em class="replaceable"><code>source_table_name</code></em> [ * ] | ( <em class="replaceable"><code>source_query</code></em> ) } [ [ AS ] <em class="replaceable"><code>source_alias</code></em> ]11 12<span class="phrase">and <em class="replaceable"><code>when_clause</code></em> is:</span>13 14{ WHEN MATCHED [ AND <em class="replaceable"><code>condition</code></em> ] THEN { <em class="replaceable"><code>merge_update</code></em> | <em class="replaceable"><code>merge_delete</code></em> | DO NOTHING } |15 WHEN NOT MATCHED [ AND <em class="replaceable"><code>condition</code></em> ] THEN { <em class="replaceable"><code>merge_insert</code></em> | DO NOTHING } }16 17<span class="phrase">and <em class="replaceable"><code>merge_insert</code></em> is:</span>18 19INSERT [( <em class="replaceable"><code>column_name</code></em> [, ...] )]20[ OVERRIDING { SYSTEM | USER } VALUE ]21{ VALUES ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) | DEFAULT VALUES }22 23<span class="phrase">and <em class="replaceable"><code>merge_update</code></em> is:</span>24 25UPDATE SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } |26 ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = [ ROW ] ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) |27 ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( <em class="replaceable"><code>sub-SELECT</code></em> )28 } [, ...]29 30<span class="phrase">and <em class="replaceable"><code>merge_delete</code></em> is:</span>31 32DELETE33</pre></div><div class="refsect1" id="id-1.9.3.156.5"><h2>Description</h2><p>34 <code class="command">MERGE</code> performs actions that modify rows in the35 target table identified as <em class="replaceable"><code>target_table_name</code></em>,36 using the <em class="replaceable"><code>data_source</code></em>.37 <code class="command">MERGE</code> provides a single <acronym class="acronym">SQL</acronym>38 statement that can conditionally <code class="command">INSERT</code>,39 <code class="command">UPDATE</code> or <code class="command">DELETE</code> rows, a task40 that would otherwise require multiple procedural language statements.41 </p><p>42 First, the <code class="command">MERGE</code> command performs a join43 from <em class="replaceable"><code>data_source</code></em> to44 the target table45 producing zero or more candidate change rows. For each candidate change46 row, the status of <code class="literal">MATCHED</code> or <code class="literal">NOT MATCHED</code>47 is set just once, after which <code class="literal">WHEN</code> clauses are evaluated48 in the order specified. For each candidate change row, the first clause to49 evaluate as true is executed. No more than one <code class="literal">WHEN</code>50 clause is executed for any candidate change row.51 </p><p>52 <code class="command">MERGE</code> actions have the same effect as53 regular <code class="command">UPDATE</code>, <code class="command">INSERT</code>, or54 <code class="command">DELETE</code> commands of the same names. The syntax of55 those commands is different, notably that there is no <code class="literal">WHERE</code>56 clause and no table name is specified. All actions refer to the57 target table,58 though modifications to other tables may be made using triggers.59 </p><p>60 When <code class="literal">DO NOTHING</code> is specified, the source row is61 skipped. Since actions are evaluated in their specified order, <code class="literal">DO62 NOTHING</code> can be handy to skip non-interesting source rows before63 more fine-grained handling.64 </p><p>65 There is no separate <code class="literal">MERGE</code> privilege.66 If you specify an update action, you must have the67 <code class="literal">UPDATE</code> privilege on the column(s)68 of the target table69 that are referred to in the <code class="literal">SET</code> clause.70 If you specify an insert action, you must have the <code class="literal">INSERT</code>71 privilege on the target table.72 If you specify a delete action, you must have the <code class="literal">DELETE</code>73 privilege on the target table.74 If you specify a <code class="literal">DO NOTHING</code> action, you must have75 the <code class="literal">SELECT</code> privilege on at least one column76 of the target table.77 You will also need <code class="literal">SELECT</code> privilege on any column(s)78 of the <em class="replaceable"><code>data_source</code></em> and79 of the target table referred to80 in any <code class="literal">condition</code> (including <code class="literal">join_condition</code>)81 or <code class="literal">expression</code>.82 Privileges are tested once at statement start and are checked83 whether or not particular <code class="literal">WHEN</code> clauses are executed.84 </p><p>85 <code class="command">MERGE</code> is not supported if the86 target table is a87 materialized view, foreign table, or if it has any88 rules defined on it.89 </p></div><div class="refsect1" id="id-1.9.3.156.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p>90 The <code class="literal">WITH</code> clause allows you to specify one or more91 subqueries that can be referenced by name in the <code class="command">MERGE</code>92 query. See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> and <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>93 for details. Note that <code class="literal">WITH RECURSIVE</code> is not supported94 by <code class="command">MERGE</code>.95 </p></dd><dt><span class="term"><em class="replaceable"><code>target_table_name</code></em></span></dt><dd><p>96 The name (optionally schema-qualified) of the target table to merge into.97 If <code class="literal">ONLY</code> is specified before the table name, matching98 rows are updated or deleted in the named table only. If99 <code class="literal">ONLY</code> is not specified, matching rows are also updated100 or deleted in any tables inheriting from the named table. Optionally,101 <code class="literal">*</code> can be specified after the table name to explicitly102 indicate that descendant tables are included. The103 <code class="literal">ONLY</code> keyword and <code class="literal">*</code> option do not104 affect insert actions, which always insert into the named table only.105 </p></dd><dt><span class="term"><em class="replaceable"><code>target_alias</code></em></span></dt><dd><p>106 A substitute name for the target table. When an alias is107 provided, it completely hides the actual name of the table. For108 example, given <code class="literal">MERGE INTO foo AS f</code>, the remainder of the109 <code class="command">MERGE</code> statement must refer to this table as110 <code class="literal">f</code> not <code class="literal">foo</code>.111 </p></dd><dt><span class="term"><em class="replaceable"><code>source_table_name</code></em></span></dt><dd><p>112 The name (optionally schema-qualified) of the source table, view, or113 transition table. If <code class="literal">ONLY</code> is specified before the114 table name, matching rows are included from the named table only. If115 <code class="literal">ONLY</code> is not specified, matching rows are also included116 from any tables inheriting from the named table. Optionally,117 <code class="literal">*</code> can be specified after the table name to explicitly118 indicate that descendant tables are included.119 </p></dd><dt><span class="term"><em class="replaceable"><code>source_query</code></em></span></dt><dd><p>120 A query (<code class="command">SELECT</code> statement or <code class="command">VALUES</code>121 statement) that supplies the rows to be merged into the122 target table.123 Refer to the <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>124 statement or <a class="xref" href="sql-values.html" title="VALUES"><span class="refentrytitle">VALUES</span></a>125 statement for a description of the syntax.126 </p></dd><dt><span class="term"><em class="replaceable"><code>source_alias</code></em></span></dt><dd><p>127 A substitute name for the data source. When an alias is128 provided, it completely hides the actual name of the table or the fact129 that a query was issued.130 </p></dd><dt><span class="term"><em class="replaceable"><code>join_condition</code></em></span></dt><dd><p>131 <em class="replaceable"><code>join_condition</code></em> is132 an expression resulting in a value of type133 <code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>134 clause) that specifies which rows in the135 <em class="replaceable"><code>data_source</code></em>136 match rows in the target table.137 </p><div class="warning"><h3 class="title">Warning</h3><p>138 Only columns from the target table139 that attempt to match <em class="replaceable"><code>data_source</code></em>140 rows should appear in <em class="replaceable"><code>join_condition</code></em>.141 <em class="replaceable"><code>join_condition</code></em> subexpressions that142 only reference the target table's143 columns can affect which action is taken, often in surprising ways.144 </p></div></dd><dt><span class="term"><em class="replaceable"><code>when_clause</code></em></span></dt><dd><p>145 At least one <code class="literal">WHEN</code> clause is required.146 </p><p>147 If the <code class="literal">WHEN</code> clause specifies <code class="literal">WHEN MATCHED</code>148 and the candidate change row matches a row in the149 target table,150 the <code class="literal">WHEN</code> clause is executed if the151 <em class="replaceable"><code>condition</code></em> is152 absent or it evaluates to <code class="literal">true</code>.153 </p><p>154 Conversely, if the <code class="literal">WHEN</code> clause specifies155 <code class="literal">WHEN NOT MATCHED</code>156 and the candidate change row does not match a row in the157 target table,158 the <code class="literal">WHEN</code> clause is executed if the159 <em class="replaceable"><code>condition</code></em> is160 absent or it evaluates to <code class="literal">true</code>.161 </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>162 An expression that returns a value of type <code class="type">boolean</code>.163 If this expression for a <code class="literal">WHEN</code> clause164 returns <code class="literal">true</code>, then the action for that clause165 is executed for that row.166 </p><p>167 A condition on a <code class="literal">WHEN MATCHED</code> clause can refer to columns168 in both the source and the target relations. A condition on a169 <code class="literal">WHEN NOT MATCHED</code> clause can only refer to columns from170 the source relation, since by definition there is no matching target row.171 Only the system attributes from the target table are accessible.172 </p></dd><dt><span class="term"><em class="replaceable"><code>merge_insert</code></em></span></dt><dd><p>173 The specification of an <code class="literal">INSERT</code> action that inserts174 one row into the target table.175 The target column names can be listed in any order. If no list of176 column names is given at all, the default is all the columns of the177 table in their declared order.178 </p><p>179 Each column not present in the explicit or implicit column list will be180 filled with a default value, either its declared default value181 or null if there is none.182 </p><p>183 If the target table184 is a partitioned table, each row is routed to the appropriate partition185 and inserted into it.186 If the target table187 is a partition, an error will occur if any input row violates the188 partition constraint.189 </p><p>190 Column names may not be specified more than once.191 <code class="command">INSERT</code> actions cannot contain sub-selects.192 </p><p>193 Only one <code class="literal">VALUES</code> clause can be specified.194 The <code class="literal">VALUES</code> clause can only refer to columns from195 the source relation, since by definition there is no matching target row.196 </p></dd><dt><span class="term"><em class="replaceable"><code>merge_update</code></em></span></dt><dd><p>197 The specification of an <code class="literal">UPDATE</code> action that updates198 the current row of the target table.199 Column names may not be specified more than once.200 </p><p>201 Neither a table name nor a <code class="literal">WHERE</code> clause are allowed.202 </p></dd><dt><span class="term"><em class="replaceable"><code>merge_delete</code></em></span></dt><dd><p>203 Specifies a <code class="literal">DELETE</code> action that deletes the current row204 of the target table.205 Do not include the table name or any other clauses, as you would normally206 do with a <a class="xref" href="sql-delete.html" title="DELETE"><span class="refentrytitle">DELETE</span></a> command.207 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>208 The name of a column in the target table. The column name209 can be qualified with a subfield name or array subscript, if210 needed. (Inserting into only some fields of a composite211 column leaves the other fields null.)212 Do not include the table's name in the specification213 of a target column.214 </p></dd><dt><span class="term"><code class="literal">OVERRIDING SYSTEM VALUE</code></span></dt><dd><p>215 Without this clause, it is an error to specify an explicit value216 (other than <code class="literal">DEFAULT</code>) for an identity column defined217 as <code class="literal">GENERATED ALWAYS</code>. This clause overrides that218 restriction.219 </p></dd><dt><span class="term"><code class="literal">OVERRIDING USER VALUE</code></span></dt><dd><p>220 If this clause is specified, then any values supplied for identity221 columns defined as <code class="literal">GENERATED BY DEFAULT</code> are ignored222 and the default sequence-generated values are applied.223 </p></dd><dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt><dd><p>224 All columns will be filled with their default values.225 (An <code class="literal">OVERRIDING</code> clause is not permitted in this226 form.)227 </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p>228 An expression to assign to the column. If used in a229 <code class="literal">WHEN MATCHED</code> clause, the expression can use values230 from the original row in the target table, and values from the231 <em class="replaceable"><code>data_source</code></em> row.232 If used in a <code class="literal">WHEN NOT MATCHED</code> clause, the233 expression can use values from the234 <em class="replaceable"><code>data_source</code></em> row.235 </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>236 Set the column to its default value (which will be <code class="literal">NULL</code>237 if no specific default expression has been assigned to it).238 </p></dd><dt><span class="term"><em class="replaceable"><code>sub-SELECT</code></em></span></dt><dd><p>239 A <code class="literal">SELECT</code> sub-query that produces as many output columns240 as are listed in the parenthesized column list preceding it. The241 sub-query must yield no more than one row when executed. If it242 yields one row, its column values are assigned to the target columns;243 if it yields no rows, NULL values are assigned to the target columns.244 The sub-query can refer to values from the original row in the target table,245 and values from the <em class="replaceable"><code>data_source</code></em>246 row.247 </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.156.7"><h2>Outputs</h2><p>248 On successful completion, a <code class="command">MERGE</code> command returns a command249 tag of the form250</p><pre class="screen">251MERGE <em class="replaceable"><code>total_count</code></em>252</pre><p>253 The <em class="replaceable"><code>total_count</code></em> is the total254 number of rows changed (whether inserted, updated, or deleted).255 If <em class="replaceable"><code>total_count</code></em> is 0, no rows256 were changed in any way.257 </p></div><div class="refsect1" id="id-1.9.3.156.8"><h2>Notes</h2><p>258 The following steps take place during the execution of259 <code class="command">MERGE</code>.260 </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>261 Perform any <code class="literal">BEFORE STATEMENT</code> triggers for all262 actions specified, whether or not their <code class="literal">WHEN</code>263 clauses match.264 </p></li><li class="listitem"><p>265 Perform a join from source to target table.266 The resulting query will be optimized normally and will produce267 a set of candidate change rows. For each candidate change row,268 </p><div class="orderedlist"><ol class="orderedlist" type="a"><li class="listitem"><p>269 Evaluate whether each row is <code class="literal">MATCHED</code> or270 <code class="literal">NOT MATCHED</code>.271 </p></li><li class="listitem"><p>272 Test each <code class="literal">WHEN</code> condition in the order273 specified until one returns true.274 </p></li><li class="listitem"><p>275 When a condition returns true, perform the following actions:276 </p><div class="orderedlist"><ol class="orderedlist" type="i"><li class="listitem"><p>277 Perform any <code class="literal">BEFORE ROW</code> triggers that fire278 for the action's event type.279 </p></li><li class="listitem"><p>280 Perform the specified action, invoking any check constraints on the281 target table.282 </p></li><li class="listitem"><p>283 Perform any <code class="literal">AFTER ROW</code> triggers that fire for284 the action's event type.285 </p></li></ol></div></li></ol></div></li><li class="listitem"><p>286 Perform any <code class="literal">AFTER STATEMENT</code> triggers for actions287 specified, whether or not they actually occur. This is similar to the288 behavior of an <code class="command">UPDATE</code> statement that modifies no rows.289 </p></li></ol></div><p>290 In summary, statement triggers for an event type (say,291 <code class="command">INSERT</code>) will be fired whenever we292 <span class="emphasis"><em>specify</em></span> an action of that kind.293 In contrast, row-level triggers will fire only for the specific event type294 being <span class="emphasis"><em>executed</em></span>.295 So a <code class="command">MERGE</code> command might fire statement triggers for both296 <code class="command">UPDATE</code> and <code class="command">INSERT</code>, even though only297 <code class="command">UPDATE</code> row triggers were fired.298 </p><p>299 You should ensure that the join produces at most one candidate change row300 for each target row. In other words, a target row shouldn't join to more301 than one data source row. If it does, then only one of the candidate change302 rows will be used to modify the target row; later attempts to modify the303 row will cause an error.304 This can also occur if row triggers make changes to the target table305 and the rows so modified are then subsequently also modified by306 <code class="command">MERGE</code>.307 If the repeated action is an <code class="command">INSERT</code>, this will308 cause a uniqueness violation, while a repeated <code class="command">UPDATE</code>309 or <code class="command">DELETE</code> will cause a cardinality violation; the310 latter behavior is required by the <acronym class="acronym">SQL</acronym> standard.311 This differs from historical <span class="productname">PostgreSQL</span>312 behavior of joins in <code class="command">UPDATE</code> and313 <code class="command">DELETE</code> statements where second and subsequent314 attempts to modify the same row are simply ignored.315 </p><p>316 If a <code class="literal">WHEN</code> clause omits an <code class="literal">AND</code>317 sub-clause, it becomes the final reachable clause of that318 kind (<code class="literal">MATCHED</code> or <code class="literal">NOT MATCHED</code>).319 If a later <code class="literal">WHEN</code> clause of that kind320 is specified it would be provably unreachable and an error is raised.321 If no final reachable clause is specified of either kind, it is322 possible that no action will be taken for a candidate change row.323 </p><p>324 The order in which rows are generated from the data source is325 indeterminate by default.326 A <em class="replaceable"><code>source_query</code></em> can be327 used to specify a consistent ordering, if required, which might be328 needed to avoid deadlocks between concurrent transactions.329 </p><p>330 There is no <code class="literal">RETURNING</code> clause with331 <code class="command">MERGE</code>. Actions of <code class="command">INSERT</code>,332 <code class="command">UPDATE</code> and <code class="command">DELETE</code> cannot contain333 <code class="literal">RETURNING</code> or <code class="literal">WITH</code> clauses.334 </p><p>335 When <code class="command">MERGE</code> is run concurrently with other commands336 that modify the target table, the usual transaction isolation rules337 apply; see <a class="xref" href="transaction-iso.html" title="13.2. Transaction Isolation">Section 13.2</a> for an explanation338 on the behavior at each isolation level.339 You may also wish to consider using <code class="command">INSERT ... ON CONFLICT</code>340 as an alternative statement which offers the ability to run an341 <code class="command">UPDATE</code> if a concurrent <code class="command">INSERT</code>342 occurs. There are a variety of differences and restrictions between343 the two statement types and they are not interchangeable.344 </p></div><div class="refsect1" id="id-1.9.3.156.9"><h2>Examples</h2><p>345 Perform maintenance on <code class="literal">customer_accounts</code> based346 upon new <code class="literal">recent_transactions</code>.347 348</p><pre class="programlisting">349MERGE INTO customer_account ca350USING recent_transactions t351ON t.customer_id = ca.customer_id352WHEN MATCHED THEN353 UPDATE SET balance = balance + transaction_value354WHEN NOT MATCHED THEN355 INSERT (customer_id, balance)356 VALUES (t.customer_id, t.transaction_value);357</pre><p>358 </p><p>359 Notice that this would be exactly equivalent to the following360 statement because the <code class="literal">MATCHED</code> result does not change361 during execution.362 363</p><pre class="programlisting">364MERGE INTO customer_account ca365USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t366ON t.customer_id = ca.customer_id367WHEN MATCHED THEN368 UPDATE SET balance = balance + transaction_value369WHEN NOT MATCHED THEN370 INSERT (customer_id, balance)371 VALUES (t.customer_id, t.transaction_value);372</pre><p>373 </p><p>374 Attempt to insert a new stock item along with the quantity of stock. If375 the item already exists, instead update the stock count of the existing376 item. Don't allow entries that have zero stock.377</p><pre class="programlisting">378MERGE INTO wines w379USING wine_stock_changes s380ON s.winename = w.winename381WHEN NOT MATCHED AND s.stock_delta > 0 THEN382 INSERT VALUES(s.winename, s.stock_delta)383WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN384 UPDATE SET stock = w.stock + s.stock_delta385WHEN MATCHED THEN386 DELETE;387</pre><p>388 389 The <code class="literal">wine_stock_changes</code> table might be, for example, a390 temporary table recently loaded into the database.391 </p></div><div class="refsect1" id="id-1.9.3.156.10"><h2>Compatibility</h2><p>392 This command conforms to the <acronym class="acronym">SQL</acronym> standard.393 </p><p>394 The <code class="literal">WITH</code> clause and <code class="literal">DO NOTHING</code>395 action are extensions to the <acronym class="acronym">SQL</acronym> standard.396 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-lock.html" title="LOCK">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-move.html" title="MOVE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">LOCK </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"> MOVE</td></tr></table></div></body></html>