summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/man7/MERGE.7
blob: 87bd747b5109ee159186e6f54493bcce132b70bf (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
'\" t
.\"     Title: MERGE
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2024
.\"    Manual: PostgreSQL 15.7 Documentation
.\"    Source: PostgreSQL 15.7
.\"  Language: English
.\"
.TH "MERGE" "7" "2024" "PostgreSQL 15.7" "PostgreSQL 15.7 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
MERGE \- conditionally insert, update, or delete rows of a table
.SH "SYNOPSIS"
.sp
.nf
[ WITH \fIwith_query\fR [, \&.\&.\&.] ]
MERGE INTO [ ONLY ] \fItarget_table_name\fR [ * ] [ [ AS ] \fItarget_alias\fR ]
USING \fIdata_source\fR ON \fIjoin_condition\fR
\fIwhen_clause\fR [\&.\&.\&.]

where \fIdata_source\fR is:

{ [ ONLY ] \fIsource_table_name\fR [ * ] | ( \fIsource_query\fR ) } [ [ AS ] \fIsource_alias\fR ]

and \fIwhen_clause\fR is:

{ WHEN MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_update\fR | \fImerge_delete\fR | DO NOTHING } |
  WHEN NOT MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_insert\fR | DO NOTHING } }

and \fImerge_insert\fR is:

INSERT [( \fIcolumn_name\fR [, \&.\&.\&.] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) | DEFAULT VALUES }

and \fImerge_update\fR is:

UPDATE SET { \fIcolumn_name\fR = { \fIexpression\fR | DEFAULT } |
             ( \fIcolumn_name\fR [, \&.\&.\&.] ) = [ ROW ] ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) |
             ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( \fIsub\-SELECT\fR )
           } [, \&.\&.\&.]

and \fImerge_delete\fR is:

DELETE
.fi
.SH "DESCRIPTION"
.PP
\fBMERGE\fR
performs actions that modify rows in the target table identified as
\fItarget_table_name\fR, using the
\fIdata_source\fR\&.
\fBMERGE\fR
provides a single
SQL
statement that can conditionally
\fBINSERT\fR,
\fBUPDATE\fR
or
\fBDELETE\fR
rows, a task that would otherwise require multiple procedural language statements\&.
.PP
First, the
\fBMERGE\fR
command performs a join from
\fIdata_source\fR
to the target table producing zero or more candidate change rows\&. For each candidate change row, the status of
MATCHED
or
NOT MATCHED
is set just once, after which
WHEN
clauses are evaluated in the order specified\&. For each candidate change row, the first clause to evaluate as true is executed\&. No more than one
WHEN
clause is executed for any candidate change row\&.
.PP
\fBMERGE\fR
actions have the same effect as regular
\fBUPDATE\fR,
\fBINSERT\fR, or
\fBDELETE\fR
commands of the same names\&. The syntax of those commands is different, notably that there is no
WHERE
clause and no table name is specified\&. All actions refer to the target table, though modifications to other tables may be made using triggers\&.
.PP
When
DO NOTHING
is specified, the source row is skipped\&. Since actions are evaluated in their specified order,
DO NOTHING
can be handy to skip non\-interesting source rows before more fine\-grained handling\&.
.PP
There is no separate
MERGE
privilege\&. If you specify an update action, you must have the
UPDATE
privilege on the column(s) of the target table that are referred to in the
SET
clause\&. If you specify an insert action, you must have the
INSERT
privilege on the target table\&. If you specify a delete action, you must have the
DELETE
privilege on the target table\&. If you specify a
DO NOTHING
action, you must have the
SELECT
privilege on at least one column of the target table\&. You will also need
SELECT
privilege on any column(s) of the
\fIdata_source\fR
and of the target table referred to in any
condition
(including
join_condition) or
expression\&. Privileges are tested once at statement start and are checked whether or not particular
WHEN
clauses are executed\&.
.PP
\fBMERGE\fR
is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it\&.
.SH "PARAMETERS"
.PP
\fIwith_query\fR
.RS 4
The
WITH
clause allows you to specify one or more subqueries that can be referenced by name in the
\fBMERGE\fR
query\&. See
Section\ \&7.8
and
\fBSELECT\fR(7)
for details\&. Note that
WITH RECURSIVE
is not supported by
\fBMERGE\fR\&.
.RE
.PP
\fItarget_table_name\fR
.RS 4
The name (optionally schema\-qualified) of the target table to merge into\&. If
ONLY
is specified before the table name, matching rows are updated or deleted in the named table only\&. If
ONLY
is not specified, matching rows are also updated or deleted in any tables inheriting from the named table\&. Optionally,
*
can be specified after the table name to explicitly indicate that descendant tables are included\&. The
ONLY
keyword and
*
option do not affect insert actions, which always insert into the named table only\&.
.RE
.PP
\fItarget_alias\fR
.RS 4
A substitute name for the target table\&. When an alias is provided, it completely hides the actual name of the table\&. For example, given
MERGE INTO foo AS f, the remainder of the
\fBMERGE\fR
statement must refer to this table as
f
not
foo\&.
.RE
.PP
\fIsource_table_name\fR
.RS 4
The name (optionally schema\-qualified) of the source table, view, or transition table\&. If
ONLY
is specified before the table name, matching rows are included from the named table only\&. If
ONLY
is not specified, matching rows are also included from any tables inheriting from the named table\&. Optionally,
*
can be specified after the table name to explicitly indicate that descendant tables are included\&.
.RE
.PP
\fIsource_query\fR
.RS 4
A query (\fBSELECT\fR
statement or
\fBVALUES\fR
statement) that supplies the rows to be merged into the target table\&. Refer to the
\fBSELECT\fR(7)
statement or
\fBVALUES\fR(7)
statement for a description of the syntax\&.
.RE
.PP
\fIsource_alias\fR
.RS 4
A substitute name for the data source\&. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued\&.
.RE
.PP
\fIjoin_condition\fR
.RS 4
\fIjoin_condition\fR
is an expression resulting in a value of type
boolean
(similar to a
WHERE
clause) that specifies which rows in the
\fIdata_source\fR
match rows in the target table\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBWarning\fR
.ps -1
.br
Only columns from the target table that attempt to match
\fIdata_source\fR
rows should appear in
\fIjoin_condition\fR\&.
\fIjoin_condition\fR
subexpressions that only reference the target table\*(Aqs columns can affect which action is taken, often in surprising ways\&.
.sp .5v
.RE
.RE
.PP
\fIwhen_clause\fR
.RS 4
At least one
WHEN
clause is required\&.
.sp
If the
WHEN
clause specifies
WHEN MATCHED
and the candidate change row matches a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
is absent or it evaluates to
true\&.
.sp
Conversely, if the
WHEN
clause specifies
WHEN NOT MATCHED
and the candidate change row does not match a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
is absent or it evaluates to
true\&.
.RE
.PP
\fIcondition\fR
.RS 4
An expression that returns a value of type
boolean\&. If this expression for a
WHEN
clause returns
true, then the action for that clause is executed for that row\&.
.sp
A condition on a
WHEN MATCHED
clause can refer to columns in both the source and the target relations\&. A condition on a
WHEN NOT MATCHED
clause can only refer to columns from the source relation, since by definition there is no matching target row\&. Only the system attributes from the target table are accessible\&.
.RE
.PP
\fImerge_insert\fR
.RS 4
The specification of an
INSERT
action that inserts one row into the target table\&. The target column names can be listed in any order\&. If no list of column names is given at all, the default is all the columns of the table in their declared order\&.
.sp
Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none\&.
.sp
If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it\&. If the target table is a partition, an error will occur if any input row violates the partition constraint\&.
.sp
Column names may not be specified more than once\&.
\fBINSERT\fR
actions cannot contain sub\-selects\&.
.sp
Only one
VALUES
clause can be specified\&. The
VALUES
clause can only refer to columns from the source relation, since by definition there is no matching target row\&.
.RE
.PP
\fImerge_update\fR
.RS 4
The specification of an
UPDATE
action that updates the current row of the target table\&. Column names may not be specified more than once\&.
.sp
Neither a table name nor a
WHERE
clause are allowed\&.
.RE
.PP
\fImerge_delete\fR
.RS 4
Specifies a
DELETE
action that deletes the current row of the target table\&. Do not include the table name or any other clauses, as you would normally do with a
\fBDELETE\fR(7)
command\&.
.RE
.PP
\fIcolumn_name\fR
.RS 4
The name of a column in the target table\&. The column name can be qualified with a subfield name or array subscript, if needed\&. (Inserting into only some fields of a composite column leaves the other fields null\&.) Do not include the table\*(Aqs name in the specification of a target column\&.
.RE
.PP
OVERRIDING SYSTEM VALUE
.RS 4
Without this clause, it is an error to specify an explicit value (other than
DEFAULT) for an identity column defined as
GENERATED ALWAYS\&. This clause overrides that restriction\&.
.RE
.PP
OVERRIDING USER VALUE
.RS 4
If this clause is specified, then any values supplied for identity columns defined as
GENERATED BY DEFAULT
are ignored and the default sequence\-generated values are applied\&.
.RE
.PP
DEFAULT VALUES
.RS 4
All columns will be filled with their default values\&. (An
OVERRIDING
clause is not permitted in this form\&.)
.RE
.PP
\fIexpression\fR
.RS 4
An expression to assign to the column\&. If used in a
WHEN MATCHED
clause, the expression can use values from the original row in the target table, and values from the
\fIdata_source\fR
row\&. If used in a
WHEN NOT MATCHED
clause, the expression can use values from the
\fIdata_source\fR
row\&.
.RE
.PP
DEFAULT
.RS 4
Set the column to its default value (which will be
NULL
if no specific default expression has been assigned to it)\&.
.RE
.PP
\fIsub\-SELECT\fR
.RS 4
A
SELECT
sub\-query that produces as many output columns as are listed in the parenthesized column list preceding it\&. The sub\-query must yield no more than one row when executed\&. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns\&. The sub\-query can refer to values from the original row in the target table, and values from the
\fIdata_source\fR
row\&.
.RE
.SH "OUTPUTS"
.PP
On successful completion, a
\fBMERGE\fR
command returns a command tag of the form
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE \fItotal_count\fR
.fi
.if n \{\
.RE
.\}
.sp
The
\fItotal_count\fR
is the total number of rows changed (whether inserted, updated, or deleted)\&. If
\fItotal_count\fR
is 0, no rows were changed in any way\&.
.SH "NOTES"
.PP
The following steps take place during the execution of
\fBMERGE\fR\&.
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Perform any
BEFORE STATEMENT
triggers for all actions specified, whether or not their
WHEN
clauses match\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
Perform a join from source to target table\&. The resulting query will be optimized normally and will produce a set of candidate change rows\&. For each candidate change row,
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Evaluate whether each row is
MATCHED
or
NOT MATCHED\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
Test each
WHEN
condition in the order specified until one returns true\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
When a condition returns true, perform the following actions:
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Perform any
BEFORE ROW
triggers that fire for the action\*(Aqs event type\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
Perform the specified action, invoking any check constraints on the target table\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
Perform any
AFTER ROW
triggers that fire for the action\*(Aqs event type\&.
.RE
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
Perform any
AFTER STATEMENT
triggers for actions specified, whether or not they actually occur\&. This is similar to the behavior of an
\fBUPDATE\fR
statement that modifies no rows\&.
.RE
.sp
In summary, statement triggers for an event type (say,
\fBINSERT\fR) will be fired whenever we
\fIspecify\fR
an action of that kind\&. In contrast, row\-level triggers will fire only for the specific event type being
\fIexecuted\fR\&. So a
\fBMERGE\fR
command might fire statement triggers for both
\fBUPDATE\fR
and
\fBINSERT\fR, even though only
\fBUPDATE\fR
row triggers were fired\&.
.PP
You should ensure that the join produces at most one candidate change row for each target row\&. In other words, a target row shouldn\*(Aqt join to more than one data source row\&. If it does, then only one of the candidate change rows will be used to modify the target row; later attempts to modify the row will cause an error\&. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by
\fBMERGE\fR\&. If the repeated action is an
\fBINSERT\fR, this will cause a uniqueness violation, while a repeated
\fBUPDATE\fR
or
\fBDELETE\fR
will cause a cardinality violation; the latter behavior is required by the
SQL
standard\&. This differs from historical
PostgreSQL
behavior of joins in
\fBUPDATE\fR
and
\fBDELETE\fR
statements where second and subsequent attempts to modify the same row are simply ignored\&.
.PP
If a
WHEN
clause omits an
AND
sub\-clause, it becomes the final reachable clause of that kind (MATCHED
or
NOT MATCHED)\&. If a later
WHEN
clause of that kind is specified it would be provably unreachable and an error is raised\&. If no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row\&.
.PP
The order in which rows are generated from the data source is indeterminate by default\&. A
\fIsource_query\fR
can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions\&.
.PP
There is no
RETURNING
clause with
\fBMERGE\fR\&. Actions of
\fBINSERT\fR,
\fBUPDATE\fR
and
\fBDELETE\fR
cannot contain
RETURNING
or
WITH
clauses\&.
.PP
When
\fBMERGE\fR
is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see
Section\ \&13.2
for an explanation on the behavior at each isolation level\&. You may also wish to consider using
\fBINSERT \&.\&.\&. ON CONFLICT\fR
as an alternative statement which offers the ability to run an
\fBUPDATE\fR
if a concurrent
\fBINSERT\fR
occurs\&. There are a variety of differences and restrictions between the two statement types and they are not interchangeable\&.
.SH "EXAMPLES"
.PP
Perform maintenance on
customer_accounts
based upon new
recent_transactions\&.
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE INTO customer_account ca
USING recent_transactions t
ON t\&.customer_id = ca\&.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t\&.customer_id, t\&.transaction_value);
.fi
.if n \{\
.RE
.\}
.PP
Notice that this would be exactly equivalent to the following statement because the
MATCHED
result does not change during execution\&.
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t\&.customer_id = ca\&.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t\&.customer_id, t\&.transaction_value);
.fi
.if n \{\
.RE
.\}
.PP
Attempt to insert a new stock item along with the quantity of stock\&. If the item already exists, instead update the stock count of the existing item\&. Don\*(Aqt allow entries that have zero stock\&.
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE INTO wines w
USING wine_stock_changes s
ON s\&.winename = w\&.winename
WHEN NOT MATCHED AND s\&.stock_delta > 0 THEN
  INSERT VALUES(s\&.winename, s\&.stock_delta)
WHEN MATCHED AND w\&.stock + s\&.stock_delta > 0 THEN
  UPDATE SET stock = w\&.stock + s\&.stock_delta
WHEN MATCHED THEN
  DELETE;
.fi
.if n \{\
.RE
.\}
.sp
The
wine_stock_changes
table might be, for example, a temporary table recently loaded into the database\&.
.SH "COMPATIBILITY"
.PP
This command conforms to the
SQL
standard\&.
.PP
The
WITH
clause and
DO NOTHING
action are extensions to the
SQL
standard\&.