| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/merge.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-merge"> |
| 7 |
<indexterm zone="sql-merge"> |
| 8 |
<primary>MERGE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>MERGE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>MERGE</refname> |
| 19 |
<refpurpose>conditionally insert, update, or delete rows of a table</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
[ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ] |
| 25 |
MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ] |
| 26 |
USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable> |
| 27 |
<replaceable class="parameter">when_clause</replaceable> [...] |
| 28 |
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] |
| 29 |
{ * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] |
| 30 |
|
| 31 |
<phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase> |
| 32 |
|
| 33 |
{ [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ] |
| 34 |
|
| 35 |
<phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase> |
| 36 |
|
| 37 |
{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | |
| 38 |
WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | |
| 39 |
WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } } |
| 40 |
|
| 41 |
<phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase> |
| 42 |
|
| 43 |
INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] |
| 44 |
[ OVERRIDING { SYSTEM | USER } VALUE ] |
| 45 |
{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES } |
| 46 |
|
| 47 |
<phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase> |
| 48 |
|
| 49 |
UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | |
| 50 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | |
| 51 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) |
| 52 |
} [, ...] |
| 53 |
|
| 54 |
<phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase> |
| 55 |
|
| 56 |
DELETE |
| 57 |
</synopsis> |
| 58 |
</refsynopsisdiv> |
| 59 |
|
| 60 |
<refsect1> |
| 61 |
<title>Description</title> |
| 62 |
|
| 63 |
<para> |
| 64 |
<command>MERGE</command> performs actions that modify rows in the |
| 65 |
target table identified as <replaceable class="parameter">target_table_name</replaceable>, |
| 66 |
using the <replaceable class="parameter">data_source</replaceable>. |
| 67 |
<command>MERGE</command> provides a single <acronym>SQL</acronym> |
| 68 |
statement that can conditionally <command>INSERT</command>, |
| 69 |
<command>UPDATE</command> or <command>DELETE</command> rows, a task |
| 70 |
that would otherwise require multiple procedural language statements. |
| 71 |
</para> |
| 72 |
|
| 73 |
<para> |
| 74 |
First, the <command>MERGE</command> command performs a join |
| 75 |
from <replaceable class="parameter">data_source</replaceable> to |
| 76 |
the target table |
| 77 |
producing zero or more candidate change rows. For each candidate change |
| 78 |
row, the status of <literal>MATCHED</literal>, |
| 79 |
<literal>NOT MATCHED BY SOURCE</literal>, |
| 80 |
or <literal>NOT MATCHED [BY TARGET]</literal> |
| 81 |
is set just once, after which <literal>WHEN</literal> clauses are evaluated |
| 82 |
in the order specified. For each candidate change row, the first clause to |
| 83 |
evaluate as true is executed. No more than one <literal>WHEN</literal> |
| 84 |
clause is executed for any candidate change row. |
| 85 |
</para> |
| 86 |
|
| 87 |
<para> |
| 88 |
<command>MERGE</command> actions have the same effect as |
| 89 |
regular <command>UPDATE</command>, <command>INSERT</command>, or |
| 90 |
<command>DELETE</command> commands of the same names. The syntax of |
| 91 |
those commands is different, notably that there is no <literal>WHERE</literal> |
| 92 |
clause and no table name is specified. All actions refer to the |
| 93 |
target table, |
| 94 |
though modifications to other tables may be made using triggers. |
| 95 |
</para> |
| 96 |
|
| 97 |
<para> |
| 98 |
When <literal>DO NOTHING</literal> is specified, the source row is |
| 99 |
skipped. Since actions are evaluated in their specified order, <literal>DO |
| 100 |
NOTHING</literal> can be handy to skip non-interesting source rows before |
| 101 |
more fine-grained handling. |
| 102 |
</para> |
| 103 |
|
| 104 |
<para> |
| 105 |
The optional <literal>RETURNING</literal> clause causes <command>MERGE</command> |
| 106 |
to compute and return value(s) based on each row inserted, updated, or |
| 107 |
deleted. Any expression using the source or target table's columns, or |
| 108 |
the <link linkend="merge-action"><function>merge_action()</function></link> |
| 109 |
function can be computed. By default, when an <command>INSERT</command> or |
| 110 |
<command>UPDATE</command> action is performed, the new values of the target |
| 111 |
table's columns are used, and when a <command>DELETE</command> is performed, |
| 112 |
the old values of the target table's columns are used, but it is also |
| 113 |
possible to explicitly request old and new values. The syntax of the |
| 114 |
<literal>RETURNING</literal> list is identical to that of the output list |
| 115 |
of <command>SELECT</command>. |
| 116 |
</para> |
| 117 |
|
| 118 |
<para> |
| 119 |
There is no separate <literal>MERGE</literal> privilege. |
| 120 |
If you specify an update action, you must have the |
| 121 |
<literal>UPDATE</literal> privilege on the column(s) |
| 122 |
of the target table |
| 123 |
that are referred to in the <literal>SET</literal> clause. |
| 124 |
If you specify an insert action, you must have the <literal>INSERT</literal> |
| 125 |
privilege on the target table. |
| 126 |
If you specify a delete action, you must have the <literal>DELETE</literal> |
| 127 |
privilege on the target table. |
| 128 |
If you specify a <literal>DO NOTHING</literal> action, you must have |
| 129 |
the <literal>SELECT</literal> privilege on at least one column |
| 130 |
of the target table. |
| 131 |
You will also need <literal>SELECT</literal> privilege on any column(s) |
| 132 |
of the <replaceable class="parameter">data_source</replaceable> and |
| 133 |
of the target table referred to |
| 134 |
in any <literal>condition</literal> (including <literal>join_condition</literal>) |
| 135 |
or <literal>expression</literal>. |
| 136 |
Privileges are tested once at statement start and are checked |
| 137 |
whether or not particular <literal>WHEN</literal> clauses are executed. |
| 138 |
</para> |
| 139 |
|
| 140 |
<para> |
| 141 |
<command>MERGE</command> is not supported if the |
| 142 |
target table is a |
| 143 |
materialized view, foreign table, or if it has any |
| 144 |
rules defined on it. |
| 145 |
</para> |
| 146 |
</refsect1> |
| 147 |
|
| 148 |
<refsect1> |
| 149 |
<title>Parameters</title> |
| 150 |
|
| 151 |
<variablelist> |
| 152 |
<varlistentry> |
| 153 |
<term><replaceable class="parameter">with_query</replaceable></term> |
| 154 |
<listitem> |
| 155 |
<para> |
| 156 |
The <literal>WITH</literal> clause allows you to specify one or more |
| 157 |
subqueries that can be referenced by name in the <command>MERGE</command> |
| 158 |
query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/> |
| 159 |
for details. Note that <literal>WITH RECURSIVE</literal> is not supported |
| 160 |
by <command>MERGE</command>. |
| 161 |
</para> |
| 162 |
</listitem> |
| 163 |
</varlistentry> |
| 164 |
|
| 165 |
<varlistentry> |
| 166 |
<term><replaceable class="parameter">target_table_name</replaceable></term> |
| 167 |
<listitem> |
| 168 |
<para> |
| 169 |
The name (optionally schema-qualified) of the target table or view to |
| 170 |
merge into. If <literal>ONLY</literal> is specified before a table |
| 171 |
name, matching rows are updated or deleted in the named table only. If |
| 172 |
<literal>ONLY</literal> is not specified, matching rows are also updated |
| 173 |
or deleted in any tables inheriting from the named table. Optionally, |
| 174 |
<literal>*</literal> can be specified after the table name to explicitly |
| 175 |
indicate that descendant tables are included. The |
| 176 |
<literal>ONLY</literal> keyword and <literal>*</literal> option do not |
| 177 |
affect insert actions, which always insert into the named table only. |
| 178 |
</para> |
| 179 |
|
| 180 |
<para> |
| 181 |
If <replaceable class="parameter">target_table_name</replaceable> is a |
| 182 |
view, it must either be automatically updatable with no |
| 183 |
<literal>INSTEAD OF</literal> triggers, or it must have |
| 184 |
<literal>INSTEAD OF</literal> triggers for every type of action |
| 185 |
(<literal>INSERT</literal>, <literal>UPDATE</literal>, and |
| 186 |
<literal>DELETE</literal>) specified in the <literal>WHEN</literal> |
| 187 |
clauses. Views with rules are not supported. |
| 188 |
</para> |
| 189 |
</listitem> |
| 190 |
</varlistentry> |
| 191 |
|
| 192 |
<varlistentry> |
| 193 |
<term><replaceable class="parameter">target_alias</replaceable></term> |
| 194 |
<listitem> |
| 195 |
<para> |
| 196 |
A substitute name for the target table. When an alias is |
| 197 |
provided, it completely hides the actual name of the table. For |
| 198 |
example, given <literal>MERGE INTO foo AS f</literal>, the remainder of the |
| 199 |
<command>MERGE</command> statement must refer to this table as |
| 200 |
<literal>f</literal> not <literal>foo</literal>. |
| 201 |
</para> |
| 202 |
</listitem> |
| 203 |
</varlistentry> |
| 204 |
|
| 205 |
<varlistentry> |
| 206 |
<term><replaceable class="parameter">source_table_name</replaceable></term> |
| 207 |
<listitem> |
| 208 |
<para> |
| 209 |
The name (optionally schema-qualified) of the source table, view, or |
| 210 |
transition table. If <literal>ONLY</literal> is specified before the |
| 211 |
table name, matching rows are included from the named table only. If |
| 212 |
<literal>ONLY</literal> is not specified, matching rows are also included |
| 213 |
from any tables inheriting from the named table. Optionally, |
| 214 |
<literal>*</literal> can be specified after the table name to explicitly |
| 215 |
indicate that descendant tables are included. |
| 216 |
</para> |
| 217 |
</listitem> |
| 218 |
</varlistentry> |
| 219 |
|
| 220 |
<varlistentry> |
| 221 |
<term><replaceable class="parameter">source_query</replaceable></term> |
| 222 |
<listitem> |
| 223 |
<para> |
| 224 |
A query (<command>SELECT</command> statement or <command>VALUES</command> |
| 225 |
statement) that supplies the rows to be merged into the |
| 226 |
target table. |
| 227 |
Refer to the <xref linkend="sql-select"/> |
| 228 |
statement or <xref linkend="sql-values"/> |
| 229 |
statement for a description of the syntax. |
| 230 |
</para> |
| 231 |
</listitem> |
| 232 |
</varlistentry> |
| 233 |
|
| 234 |
<varlistentry> |
| 235 |
<term><replaceable class="parameter">source_alias</replaceable></term> |
| 236 |
<listitem> |
| 237 |
<para> |
| 238 |
A substitute name for the data source. When an alias is |
| 239 |
provided, it completely hides the actual name of the table or the fact |
| 240 |
that a query was issued. |
| 241 |
</para> |
| 242 |
</listitem> |
| 243 |
</varlistentry> |
| 244 |
|
| 245 |
<varlistentry> |
| 246 |
<term><replaceable class="parameter">join_condition</replaceable></term> |
| 247 |
<listitem> |
| 248 |
<para> |
| 249 |
<replaceable class="parameter">join_condition</replaceable> is |
| 250 |
an expression resulting in a value of type |
| 251 |
<type>boolean</type> (similar to a <literal>WHERE</literal> |
| 252 |
clause) that specifies which rows in the |
| 253 |
<replaceable class="parameter">data_source</replaceable> |
| 254 |
match rows in the target table. |
| 255 |
</para> |
| 256 |
<warning> |
| 257 |
<para> |
| 258 |
Only columns from the target table |
| 259 |
that attempt to match <replaceable class="parameter">data_source</replaceable> |
| 260 |
rows should appear in <replaceable class="parameter">join_condition</replaceable>. |
| 261 |
<replaceable class="parameter">join_condition</replaceable> subexpressions that |
| 262 |
only reference the target table's |
| 263 |
columns can affect which action is taken, often in surprising ways. |
| 264 |
</para> |
| 265 |
<para> |
| 266 |
If both <literal>WHEN NOT MATCHED BY SOURCE</literal> and |
| 267 |
<literal>WHEN NOT MATCHED [BY TARGET]</literal> clauses are specified, |
| 268 |
the <command>MERGE</command> command will perform a <literal>FULL</literal> |
| 269 |
join between <replaceable class="parameter">data_source</replaceable> |
| 270 |
and the target table. For this to work, at least one |
| 271 |
<replaceable class="parameter">join_condition</replaceable> subexpression |
| 272 |
must use an operator that can support a hash join, or all of the |
| 273 |
subexpressions must use operators that can support a merge join. |
| 274 |
</para> |
| 275 |
</warning> |
| 276 |
</listitem> |
| 277 |
</varlistentry> |
| 278 |
|
| 279 |
<varlistentry> |
| 280 |
<term><replaceable class="parameter">when_clause</replaceable></term> |
| 281 |
<listitem> |
| 282 |
<para> |
| 283 |
At least one <literal>WHEN</literal> clause is required. |
| 284 |
</para> |
| 285 |
<para> |
| 286 |
The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>, |
| 287 |
<literal>WHEN NOT MATCHED BY SOURCE</literal>, or |
| 288 |
<literal>WHEN NOT MATCHED [BY TARGET]</literal>. |
| 289 |
Note that the <acronym>SQL</acronym> standard only defines |
| 290 |
<literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal> |
| 291 |
(which is defined to mean no matching target row). |
| 292 |
<literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the |
| 293 |
<acronym>SQL</acronym> standard, as is the option to append |
| 294 |
<literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to |
| 295 |
make its meaning more explicit. |
| 296 |
</para> |
| 297 |
<para> |
| 298 |
If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal> |
| 299 |
and the candidate change row matches a row in the |
| 300 |
<replaceable class="parameter">data_source</replaceable> to a row in the |
| 301 |
target table, the <literal>WHEN</literal> clause is executed if the |
| 302 |
<replaceable class="parameter">condition</replaceable> is |
| 303 |
absent or it evaluates to <literal>true</literal>. |
| 304 |
</para> |
| 305 |
<para> |
| 306 |
If the <literal>WHEN</literal> clause specifies |
| 307 |
<literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change |
| 308 |
row represents a row in the target table that does not match a row in the |
| 309 |
<replaceable class="parameter">data_source</replaceable>, the |
| 310 |
<literal>WHEN</literal> clause is executed if the |
| 311 |
<replaceable class="parameter">condition</replaceable> is |
| 312 |
absent or it evaluates to <literal>true</literal>. |
| 313 |
</para> |
| 314 |
<para> |
| 315 |
If the <literal>WHEN</literal> clause specifies |
| 316 |
<literal>WHEN NOT MATCHED [BY TARGET]</literal> and the candidate change |
| 317 |
row represents a row in the |
| 318 |
<replaceable class="parameter">data_source</replaceable> that does not |
| 319 |
match a row in the target table, |
| 320 |
the <literal>WHEN</literal> clause is executed if the |
| 321 |
<replaceable class="parameter">condition</replaceable> is |
| 322 |
absent or it evaluates to <literal>true</literal>. |
| 323 |
</para> |
| 324 |
</listitem> |
| 325 |
</varlistentry> |
| 326 |
|
| 327 |
<varlistentry> |
| 328 |
<term><replaceable class="parameter">condition</replaceable></term> |
| 329 |
<listitem> |
| 330 |
<para> |
| 331 |
An expression that returns a value of type <type>boolean</type>. |
| 332 |
If this expression for a <literal>WHEN</literal> clause |
| 333 |
returns <literal>true</literal>, then the action for that clause |
| 334 |
is executed for that row. |
| 335 |
</para> |
| 336 |
<para> |
| 337 |
A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns |
| 338 |
in both the source and the target relations. A condition on a |
| 339 |
<literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to |
| 340 |
columns from the target relation, since by definition there is no matching |
| 341 |
source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal> |
| 342 |
clause can only refer to columns from |
| 343 |
the source relation, since by definition there is no matching target row. |
| 344 |
Only the system attributes from the target table are accessible. |
| 345 |
</para> |
| 346 |
</listitem> |
| 347 |
</varlistentry> |
| 348 |
|
| 349 |
<varlistentry> |
| 350 |
<term><replaceable class="parameter">merge_insert</replaceable></term> |
| 351 |
<listitem> |
| 352 |
<para> |
| 353 |
The specification of an <literal>INSERT</literal> action that inserts |
| 354 |
one row into the target table. |
| 355 |
The target column names can be listed in any order. If no list of |
| 356 |
column names is given at all, the default is all the columns of the |
| 357 |
table in their declared order. |
| 358 |
</para> |
| 359 |
<para> |
| 360 |
Each column not present in the explicit or implicit column list will be |
| 361 |
filled with a default value, either its declared default value |
| 362 |
or null if there is none. |
| 363 |
</para> |
| 364 |
<para> |
| 365 |
If the target table |
| 366 |
is a partitioned table, each row is routed to the appropriate partition |
| 367 |
and inserted into it. |
| 368 |
If the target table |
| 369 |
is a partition, an error will occur if any input row violates the |
| 370 |
partition constraint. |
| 371 |
</para> |
| 372 |
<para> |
| 373 |
Column names may not be specified more than once. |
| 374 |
<command>INSERT</command> actions cannot contain sub-selects. |
| 375 |
</para> |
| 376 |
<para> |
| 377 |
Only one <literal>VALUES</literal> clause can be specified. |
| 378 |
The <literal>VALUES</literal> clause can only refer to columns from |
| 379 |
the source relation, since by definition there is no matching target row. |
| 380 |
</para> |
| 381 |
</listitem> |
| 382 |
</varlistentry> |
| 383 |
|
| 384 |
<varlistentry> |
| 385 |
<term><replaceable class="parameter">merge_update</replaceable></term> |
| 386 |
<listitem> |
| 387 |
<para> |
| 388 |
The specification of an <literal>UPDATE</literal> action that updates |
| 389 |
the current row of the target table. |
| 390 |
Column names may not be specified more than once. |
| 391 |
</para> |
| 392 |
<para> |
| 393 |
Neither a table name nor a <literal>WHERE</literal> clause are allowed. |
| 394 |
</para> |
| 395 |
</listitem> |
| 396 |
</varlistentry> |
| 397 |
|
| 398 |
<varlistentry> |
| 399 |
<term><replaceable class="parameter">merge_delete</replaceable></term> |
| 400 |
<listitem> |
| 401 |
<para> |
| 402 |
Specifies a <literal>DELETE</literal> action that deletes the current row |
| 403 |
of the target table. |
| 404 |
Do not include the table name or any other clauses, as you would normally |
| 405 |
do with a <xref linkend="sql-delete"/> command. |
| 406 |
</para> |
| 407 |
</listitem> |
| 408 |
</varlistentry> |
| 409 |
|
| 410 |
<varlistentry> |
| 411 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 412 |
<listitem> |
| 413 |
<para> |
| 414 |
The name of a column in the target table. The column name |
| 415 |
can be qualified with a subfield name or array subscript, if |
| 416 |
needed. (Inserting into only some fields of a composite |
| 417 |
column leaves the other fields null.) |
| 418 |
Do not include the table's name in the specification |
| 419 |
of a target column. |
| 420 |
</para> |
| 421 |
</listitem> |
| 422 |
</varlistentry> |
| 423 |
|
| 424 |
<varlistentry> |
| 425 |
<term><literal>OVERRIDING SYSTEM VALUE</literal></term> |
| 426 |
<listitem> |
| 427 |
<para> |
| 428 |
Without this clause, it is an error to specify an explicit value |
| 429 |
(other than <literal>DEFAULT</literal>) for an identity column defined |
| 430 |
as <literal>GENERATED ALWAYS</literal>. This clause overrides that |
| 431 |
restriction. |
| 432 |
</para> |
| 433 |
</listitem> |
| 434 |
</varlistentry> |
| 435 |
|
| 436 |
<varlistentry> |
| 437 |
<term><literal>OVERRIDING USER VALUE</literal></term> |
| 438 |
<listitem> |
| 439 |
<para> |
| 440 |
If this clause is specified, then any values supplied for identity |
| 441 |
columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored |
| 442 |
and the default sequence-generated values are applied. |
| 443 |
</para> |
| 444 |
</listitem> |
| 445 |
</varlistentry> |
| 446 |
|
| 447 |
<varlistentry> |
| 448 |
<term><literal>DEFAULT VALUES</literal></term> |
| 449 |
<listitem> |
| 450 |
<para> |
| 451 |
All columns will be filled with their default values. |
| 452 |
(An <literal>OVERRIDING</literal> clause is not permitted in this |
| 453 |
form.) |
| 454 |
</para> |
| 455 |
</listitem> |
| 456 |
</varlistentry> |
| 457 |
|
| 458 |
<varlistentry> |
| 459 |
<term><replaceable class="parameter">expression</replaceable></term> |
| 460 |
<listitem> |
| 461 |
<para> |
| 462 |
An expression to assign to the column. If used in a |
| 463 |
<literal>WHEN MATCHED</literal> clause, the expression can use values |
| 464 |
from the original row in the target table, and values from the |
| 465 |
<replaceable class="parameter">data_source</replaceable> row. |
| 466 |
If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the |
| 467 |
expression can only use values from the original row in the target table. |
| 468 |
If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the |
| 469 |
expression can only use values from the |
| 470 |
<replaceable class="parameter">data_source</replaceable> row. |
| 471 |
</para> |
| 472 |
</listitem> |
| 473 |
</varlistentry> |
| 474 |
|
| 475 |
<varlistentry> |
| 476 |
<term><literal>DEFAULT</literal></term> |
| 477 |
<listitem> |
| 478 |
<para> |
| 479 |
Set the column to its default value (which will be <literal>NULL</literal> |
| 480 |
if no specific default expression has been assigned to it). |
| 481 |
</para> |
| 482 |
</listitem> |
| 483 |
</varlistentry> |
| 484 |
|
| 485 |
<varlistentry> |
| 486 |
<term><replaceable class="parameter">sub-SELECT</replaceable></term> |
| 487 |
<listitem> |
| 488 |
<para> |
| 489 |
A <literal>SELECT</literal> sub-query that produces as many output columns |
| 490 |
as are listed in the parenthesized column list preceding it. The |
| 491 |
sub-query must yield no more than one row when executed. If it |
| 492 |
yields one row, its column values are assigned to the target columns; |
| 493 |
if it yields no rows, NULL values are assigned to the target columns. |
| 494 |
If used in a <literal>WHEN MATCHED</literal> clause, the sub-query can |
| 495 |
refer to values from the original row in the target table, and values |
| 496 |
from the <replaceable class="parameter">data_source</replaceable> row. |
| 497 |
If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the |
| 498 |
sub-query can only refer to values from the original row in the target |
| 499 |
table. |
| 500 |
</para> |
| 501 |
</listitem> |
| 502 |
</varlistentry> |
| 503 |
|
| 504 |
<varlistentry> |
| 505 |
<term><replaceable class="parameter">output_alias</replaceable></term> |
| 506 |
<listitem> |
| 507 |
<para> |
| 508 |
An optional substitute name for <literal>OLD</literal> or |
| 509 |
<literal>NEW</literal> rows in the <literal>RETURNING</literal> list. |
| 510 |
</para> |
| 511 |
<para> |
| 512 |
By default, old values from the target table can be returned by writing |
| 513 |
<literal>OLD.<replaceable class="parameter">column_name</replaceable></literal> |
| 514 |
or <literal>OLD.*</literal>, and new values can be returned by writing |
| 515 |
<literal>NEW.<replaceable class="parameter">column_name</replaceable></literal> |
| 516 |
or <literal>NEW.*</literal>. When an alias is provided, these names are |
| 517 |
hidden and the old or new rows must be referred to using the alias. |
| 518 |
For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>. |
| 519 |
</para> |
| 520 |
</listitem> |
| 521 |
</varlistentry> |
| 522 |
|
| 523 |
<varlistentry> |
| 524 |
<term><replaceable class="parameter">output_expression</replaceable></term> |
| 525 |
<listitem> |
| 526 |
<para> |
| 527 |
An expression to be computed and returned by the <command>MERGE</command> |
| 528 |
command after each row is changed (whether inserted, updated, or deleted). |
| 529 |
The expression can use any columns of the source or target tables, or the |
| 530 |
<link linkend="merge-action"><function>merge_action()</function></link> |
| 531 |
function to return additional information about the action executed. |
| 532 |
</para> |
| 533 |
<para> |
| 534 |
Writing <literal>*</literal> will return all columns from the source |
| 535 |
table, followed by all columns from the target table. Often this will |
| 536 |
lead to a lot of duplication, since it is common for the source and |
| 537 |
target tables to have a lot of the same columns. This can be avoided by |
| 538 |
qualifying the <literal>*</literal> with the name or alias of the source |
| 539 |
or target table. |
| 540 |
</para> |
| 541 |
<para> |
| 542 |
A column name or <literal>*</literal> may also be qualified using |
| 543 |
<literal>OLD</literal> or <literal>NEW</literal>, or the corresponding |
| 544 |
<replaceable class="parameter">output_alias</replaceable> for |
| 545 |
<literal>OLD</literal> or <literal>NEW</literal>, to cause old or new |
| 546 |
values from the target table to be returned. An unqualified column |
| 547 |
name from the target table, or a column name or <literal>*</literal> |
| 548 |
qualified using the target table name or alias will return new values |
| 549 |
for <literal>INSERT</literal> and <literal>UPDATE</literal> actions, and |
| 550 |
old values for <literal>DELETE</literal> actions. |
| 551 |
</para> |
| 552 |
</listitem> |
| 553 |
</varlistentry> |
| 554 |
|
| 555 |
<varlistentry> |
| 556 |
<term><replaceable class="parameter">output_name</replaceable></term> |
| 557 |
<listitem> |
| 558 |
<para> |
| 559 |
A name to use for a returned column. |
| 560 |
</para> |
| 561 |
</listitem> |
| 562 |
</varlistentry> |
| 563 |
|
| 564 |
</variablelist> |
| 565 |
</refsect1> |
| 566 |
|
| 567 |
<refsect1> |
| 568 |
<title>Outputs</title> |
| 569 |
|
| 570 |
<para> |
| 571 |
On successful completion, a <command>MERGE</command> command returns a command |
| 572 |
tag of the form |
| 573 |
<screen> |
| 574 |
MERGE <replaceable class="parameter">total_count</replaceable> |
| 575 |
</screen> |
| 576 |
The <replaceable class="parameter">total_count</replaceable> is the total |
| 577 |
number of rows changed (whether inserted, updated, or deleted). |
| 578 |
If <replaceable class="parameter">total_count</replaceable> is 0, no rows |
| 579 |
were changed in any way. |
| 580 |
</para> |
| 581 |
|
| 582 |
<para> |
| 583 |
If the <command>MERGE</command> command contains a <literal>RETURNING</literal> |
| 584 |
clause, the result will be similar to that of a <command>SELECT</command> |
| 585 |
statement containing the columns and values defined in the |
| 586 |
<literal>RETURNING</literal> list, computed over the row(s) inserted, updated, |
| 587 |
or deleted by the command. |
| 588 |
</para> |
| 589 |
</refsect1> |
| 590 |
|
| 591 |
<refsect1> |
| 592 |
<title>Notes</title> |
| 593 |
|
| 594 |
<para> |
| 595 |
The following steps take place during the execution of |
| 596 |
<command>MERGE</command>. |
| 597 |
<orderedlist> |
| 598 |
<listitem> |
| 599 |
<para> |
| 600 |
Perform any <literal>BEFORE STATEMENT</literal> triggers for all |
| 601 |
actions specified, whether or not their <literal>WHEN</literal> |
| 602 |
clauses match. |
| 603 |
</para> |
| 604 |
</listitem> |
| 605 |
<listitem> |
| 606 |
<para> |
| 607 |
Perform a join from source to target table. |
| 608 |
The resulting query will be optimized normally and will produce |
| 609 |
a set of candidate change rows. For each candidate change row, |
| 610 |
<orderedlist> |
| 611 |
<listitem> |
| 612 |
<para> |
| 613 |
Evaluate whether each row is <literal>MATCHED</literal>, |
| 614 |
<literal>NOT MATCHED BY SOURCE</literal>, or |
| 615 |
<literal>NOT MATCHED [BY TARGET]</literal>. |
| 616 |
</para> |
| 617 |
</listitem> |
| 618 |
<listitem> |
| 619 |
<para> |
| 620 |
Test each <literal>WHEN</literal> condition in the order |
| 621 |
specified until one returns true. |
| 622 |
</para> |
| 623 |
</listitem> |
| 624 |
<listitem> |
| 625 |
<para> |
| 626 |
When a condition returns true, perform the following actions: |
| 627 |
<orderedlist> |
| 628 |
<listitem> |
| 629 |
<para> |
| 630 |
Perform any <literal>BEFORE ROW</literal> triggers that fire |
| 631 |
for the action's event type. |
| 632 |
</para> |
| 633 |
</listitem> |
| 634 |
<listitem> |
| 635 |
<para> |
| 636 |
Perform the specified action, invoking any check constraints on the |
| 637 |
target table. |
| 638 |
</para> |
| 639 |
</listitem> |
| 640 |
<listitem> |
| 641 |
<para> |
| 642 |
Perform any <literal>AFTER ROW</literal> triggers that fire for |
| 643 |
the action's event type. |
| 644 |
</para> |
| 645 |
</listitem> |
| 646 |
</orderedlist> |
| 647 |
If the target relation is a view with <literal>INSTEAD OF ROW</literal> |
| 648 |
triggers for the action's event type, they are used to perform the |
| 649 |
action instead. |
| 650 |
</para> |
| 651 |
</listitem> |
| 652 |
</orderedlist></para> |
| 653 |
</listitem> |
| 654 |
<listitem> |
| 655 |
<para> |
| 656 |
Perform any <literal>AFTER STATEMENT</literal> triggers for actions |
| 657 |
specified, whether or not they actually occur. This is similar to the |
| 658 |
behavior of an <command>UPDATE</command> statement that modifies no rows. |
| 659 |
</para> |
| 660 |
</listitem> |
| 661 |
</orderedlist> |
| 662 |
In summary, statement triggers for an event type (say, |
| 663 |
<command>INSERT</command>) will be fired whenever we |
| 664 |
<emphasis>specify</emphasis> an action of that kind. |
| 665 |
In contrast, row-level triggers will fire only for the specific event type |
| 666 |
being <emphasis>executed</emphasis>. |
| 667 |
So a <command>MERGE</command> command might fire statement triggers for both |
| 668 |
<command>UPDATE</command> and <command>INSERT</command>, even though only |
| 669 |
<command>UPDATE</command> row triggers were fired. |
| 670 |
</para> |
| 671 |
|
| 672 |
<para> |
| 673 |
You should ensure that the join produces at most one candidate change row |
| 674 |
for each target row. In other words, a target row shouldn't join to more |
| 675 |
than one data source row. If it does, then only one of the candidate change |
| 676 |
rows will be used to modify the target row; later attempts to modify the |
| 677 |
row will cause an error. |
| 678 |
This can also occur if row triggers make changes to the target table |
| 679 |
and the rows so modified are then subsequently also modified by |
| 680 |
<command>MERGE</command>. |
| 681 |
If the repeated action is an <command>INSERT</command>, this will |
| 682 |
cause a uniqueness violation, while a repeated <command>UPDATE</command> |
| 683 |
or <command>DELETE</command> will cause a cardinality violation; the |
| 684 |
latter behavior is required by the <acronym>SQL</acronym> standard. |
| 685 |
This differs from historical <productname>PostgreSQL</productname> |
| 686 |
behavior of joins in <command>UPDATE</command> and |
| 687 |
<command>DELETE</command> statements where second and subsequent |
| 688 |
attempts to modify the same row are simply ignored. |
| 689 |
</para> |
| 690 |
|
| 691 |
<para> |
| 692 |
If a <literal>WHEN</literal> clause omits an <literal>AND</literal> |
| 693 |
sub-clause, it becomes the final reachable clause of that |
| 694 |
kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>, |
| 695 |
or <literal>NOT MATCHED [BY TARGET]</literal>). |
| 696 |
If a later <literal>WHEN</literal> clause of that kind |
| 697 |
is specified it would be provably unreachable and an error is raised. |
| 698 |
If no final reachable clause is specified of either kind, it is |
| 699 |
possible that no action will be taken for a candidate change row. |
| 700 |
</para> |
| 701 |
|
| 702 |
<para> |
| 703 |
The order in which rows are generated from the data source is |
| 704 |
indeterminate by default. |
| 705 |
A <replaceable class="parameter">source_query</replaceable> can be |
| 706 |
used to specify a consistent ordering, if required, which might be |
| 707 |
needed to avoid deadlocks between concurrent transactions. |
| 708 |
</para> |
| 709 |
|
| 710 |
<para> |
| 711 |
When <command>MERGE</command> is run concurrently with other commands |
| 712 |
that modify the target table, the usual transaction isolation rules |
| 713 |
apply; see <xref linkend="transaction-iso"/> for an explanation |
| 714 |
on the behavior at each isolation level. |
| 715 |
You may also wish to consider using <command>INSERT ... ON CONFLICT</command> |
| 716 |
as an alternative statement which offers the ability to run an |
| 717 |
<command>UPDATE</command> if a concurrent <command>INSERT</command> |
| 718 |
occurs. There are a variety of differences and restrictions between |
| 719 |
the two statement types and they are not interchangeable. |
| 720 |
</para> |
| 721 |
</refsect1> |
| 722 |
|
| 723 |
<refsect1> |
| 724 |
<title>Examples</title> |
| 725 |
|
| 726 |
<para> |
| 727 |
Perform maintenance on <literal>customer_accounts</literal> based |
| 728 |
upon new <literal>recent_transactions</literal>. |
| 729 |
|
| 730 |
<programlisting> |
| 731 |
MERGE INTO customer_account ca |
| 732 |
USING recent_transactions t |
| 733 |
ON t.customer_id = ca.customer_id |
| 734 |
WHEN MATCHED THEN |
| 735 |
UPDATE SET balance = balance + transaction_value |
| 736 |
WHEN NOT MATCHED THEN |
| 737 |
INSERT (customer_id, balance) |
| 738 |
VALUES (t.customer_id, t.transaction_value); |
| 739 |
</programlisting> |
| 740 |
</para> |
| 741 |
|
| 742 |
<para> |
| 743 |
Attempt to insert a new stock item along with the quantity of stock. If |
| 744 |
the item already exists, instead update the stock count of the existing |
| 745 |
item. Don't allow entries that have zero stock. Return details of all |
| 746 |
changes made. |
| 747 |
<programlisting> |
| 748 |
MERGE INTO wines w |
| 749 |
USING wine_stock_changes s |
| 750 |
ON s.winename = w.winename |
| 751 |
WHEN NOT MATCHED AND s.stock_delta > 0 THEN |
| 752 |
INSERT VALUES(s.winename, s.stock_delta) |
| 753 |
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN |
| 754 |
UPDATE SET stock = w.stock + s.stock_delta |
| 755 |
WHEN MATCHED THEN |
| 756 |
DELETE |
| 757 |
RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock; |
| 758 |
</programlisting> |
| 759 |
|
| 760 |
The <literal>wine_stock_changes</literal> table might be, for example, a |
| 761 |
temporary table recently loaded into the database. |
| 762 |
</para> |
| 763 |
|
| 764 |
<para> |
| 765 |
Update <literal>wines</literal> based on a replacement wine list, inserting |
| 766 |
rows for any new stock, updating modified stock entries, and deleting any |
| 767 |
wines not present in the new list. |
| 768 |
<programlisting> |
| 769 |
MERGE INTO wines w |
| 770 |
USING new_wine_list s |
| 771 |
ON s.winename = w.winename |
| 772 |
WHEN NOT MATCHED BY TARGET THEN |
| 773 |
INSERT VALUES(s.winename, s.stock) |
| 774 |
WHEN MATCHED AND w.stock != s.stock THEN |
| 775 |
UPDATE SET stock = s.stock |
| 776 |
WHEN NOT MATCHED BY SOURCE THEN |
| 777 |
DELETE; |
| 778 |
</programlisting> |
| 779 |
</para> |
| 780 |
|
| 781 |
</refsect1> |
| 782 |
|
| 783 |
<refsect1> |
| 784 |
<title>Compatibility</title> |
| 785 |
<para> |
| 786 |
This command conforms to the <acronym>SQL</acronym> standard. |
| 787 |
</para> |
| 788 |
<para> |
| 789 |
The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and |
| 790 |
<literal>BY TARGET</literal> qualifiers to |
| 791 |
<literal>WHEN NOT MATCHED</literal>, <literal>DO NOTHING</literal> action, |
| 792 |
and <literal>RETURNING</literal> clause are extensions to the |
| 793 |
<acronym>SQL</acronym> standard. |
| 794 |
</para> |
| 795 |
</refsect1> |
| 796 |
</refentry> |