Subject: Re: MERGE ... WHEN NOT MATCHED BY SOURCE

From: Alvaro Herrera <[email-masked]>
Date: Tue, 21 Mar 2023 13:26:27 +0100

On 2023-Mar-21, Dean Rasheed wrote:

> Looking at it with fresh eyes though, I realise that I could have just written
> 
>     action->qual = make_and_qual((Node *) ntest, action->qual);
> 
> which is equivalent, but more concise.

Nice.

I have no further observations about this patch.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Dean Rasheed <[email-masked]>
Date: Sat, 1 Jul 2023 13:33:40 +0100

> I have no further observations about this patch.

Looking at this one afresh, it seems that the change to make Vars
outer-join aware broke it -- the Var in the qual to test whether the
source row is null needs to be marked as nullable by the join added by
transform_MERGE_to_join(). That's something that needs to be done in
transform_MERGE_to_join(), so it makes more sense to add the new qual
there rather than in transformMergeStmt().

Also, now that MERGE has ruleutils support, it's clear that adding the
qual in transformMergeStmt() isn't right anyway, since it would then
appear in the deparsed output.

So attached is an updated patch doing that, which seems neater all
round, since adding the qual is closely related to the join-type
choice, which is now a decision taken entirely in
transform_MERGE_to_join(). This requires a new "mergeSourceRelation"
field on the Query structure, but as before, it does away with the
"mergeUseOuterJoin" field.

I've also updated the ruleutils support. In the absence of any WHEN
NOT MATCHED BY SOURCE actions, this will output not-matched actions
simply as "WHEN NOT MATCHED" for backwards compatibility, and to be
SQL-standard-compliant. If there are any WHEN NOT MATCHED BY SOURCE
actions though, I think it's preferable to output explicit "BY SOURCE"
and "BY TARGET" qualifiers for all not-matched actions, to make the
meaning clearer.

Regards,
Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index f8f83d4..6ef0c2b
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -396,8 +396,8 @@
     originally matched appears later in the list of actions.
     On the other hand, if the row is concurrently updated or deleted so
     that the join condition fails, then <command>MERGE</command> will
-    evaluate the condition's <literal>NOT MATCHED</literal> actions next,
-    and execute the first one that succeeds.
+    evaluate the condition's <literal>NOT MATCHED [BY TARGET]</literal>
+    actions next, and execute the first one that succeeds.
     If <command>MERGE</command> attempts an <command>INSERT</command>
     and a unique index is present and a duplicate row is concurrently
     inserted, then a uniqueness violation error is raised;
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0995fe0..8ef121a
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -33,7 +33,8 @@ USING <replaceable class="parameter">dat
 <phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>
 
 { WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
-  WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }
+  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 } |
+  WHEN NOT MATCHED [BY TARGET] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }
 
 <phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase>
 
@@ -70,7 +71,9 @@ DELETE
    from <replaceable class="parameter">data_source</replaceable> to
    <replaceable class="parameter">target_table_name</replaceable>
    producing zero or more candidate change rows.  For each candidate change
-   row, the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal>
+   row, the status of <literal>MATCHED</literal>,
+   <literal>NOT MATCHED BY SOURCE</literal>,
+   or <literal>NOT MATCHED [BY TARGET]</literal>
    is set just once, after which <literal>WHEN</literal> 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 <literal>WHEN</literal>
@@ -226,16 +229,37 @@ DELETE
       At least one <literal>WHEN</literal> clause is required.
      </para>
      <para>
+      The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>,
+      <literal>WHEN NOT MATCHED BY SOURCE</literal>, or
+      <literal>WHEN NOT MATCHED [BY TARGET]</literal>.
+      Note that the <acronym>SQL</acronym> standard only defines
+      <literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal>
+      (which is defined to mean no matching target row).
+      <literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the
+      <acronym>SQL</acronym> standard, as is the option to append
+      <literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to
+      make its meaning more explicit.
+     </para>
+     <para>
       If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>
-      and the candidate change row matches a row in the
+      and the candidate change row matches a row in the source to a row in the
       <replaceable class="parameter">target_table_name</replaceable>,
       the <literal>WHEN</literal> clause is executed if the
       <replaceable class="parameter">condition</replaceable> is
       absent or it evaluates to <literal>true</literal>.
      </para>
      <para>
-      Conversely, if the <literal>WHEN</literal> clause specifies
-      <literal>WHEN NOT MATCHED</literal>
+      If the <literal>WHEN</literal> clause specifies
+      <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change
+      row represents a row in the
+      <replaceable class="parameter">target_table_name</replaceable> that does
+      not match a source row, the <literal>WHEN</literal> clause is executed
+      if the <replaceable class="parameter">condition</replaceable> is
+      absent or it evaluates to <literal>true</literal>.
+     </para>
+     <para>
+      If the <literal>WHEN</literal> clause specifies
+      <literal>WHEN NOT MATCHED [BY TARGET]</literal>
       and the candidate change row does not match a row in the
       <replaceable class="parameter">target_table_name</replaceable>,
       the <literal>WHEN</literal> clause is executed if the
@@ -257,7 +281,10 @@ DELETE
      <para>
       A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns
       in both the source and the target relations. A condition on a
-      <literal>WHEN NOT MATCHED</literal> clause can only refer to columns from
+      <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to
+      columns from the target relation, since by definition there is no matching
+      source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal>
+      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.
      </para>
@@ -382,8 +409,10 @@ DELETE
       <literal>WHEN MATCHED</literal> clause, the expression can use values
       from the original row in the target table, and values from the
       <literal>data_source</literal> row.
-      If used in a <literal>WHEN NOT MATCHED</literal> clause, the
-      expression can use values from the <literal>data_source</literal>.
+      If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
+      expression can only use values from the original row in the target table.
+      If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the
+      expression can only use values from the <literal>data_source</literal>.
      </para>
     </listitem>
    </varlistentry>
@@ -452,8 +481,9 @@ MERGE <replaceable class="parameter">tot
        <orderedlist>
         <listitem>
          <para>
-          Evaluate whether each row is <literal>MATCHED</literal> or
-          <literal>NOT MATCHED</literal>.
+          Evaluate whether each row is <literal>MATCHED</literal>,
+          <literal>NOT MATCHED BY SOURCE</literal>, or
+          <literal>NOT MATCHED [BY TARGET]</literal>.
          </para>
         </listitem>
         <listitem>
@@ -528,7 +558,8 @@ MERGE <replaceable class="parameter">tot
   <para>
    If a <literal>WHEN</literal> clause omits an <literal>AND</literal>
    sub-clause, it becomes the final reachable clause of that
-   kind (<literal>MATCHED</literal> or <literal>NOT MATCHED</literal>).
+   kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>,
+   or <literal>NOT MATCHED [BY TARGET]</literal>).
    If a later <literal>WHEN</literal> 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
@@ -619,6 +650,23 @@ WHEN MATCHED THEN
    temporary table recently loaded into the database.
   </para>
 
+  <para>
+   Update <literal>wine</literal> based on a replacement wine list, inserting
+   rows for any new stock, updating modified stock entries, and deleting any
+   wines not present in the new list.
+<programlisting>
+MERGE INTO wines w
+USING new_wine_list s
+ON s.winename = w.winename
+WHEN NOT MATCHED BY TARGET THEN
+  INSERT VALUES(s.winename, s.stock)
+WHEN MATCHED AND w.stock != s.stock THEN
+  UPDATE SET stock = s.stock
+WHEN NOT MATCHED BY SOURCE THEN
+  DELETE;
+</programlisting>
+  </para>
+
  </refsect1>
 
  <refsect1>
@@ -627,7 +675,10 @@ WHEN MATCHED THEN
     This command conforms to the <acronym>SQL</acronym> standard.
   </para>
    <para>
-    The WITH clause and <literal>DO NOTHING</literal> action are extensions to
+    The WITH clause, <literal>BY SOURCE</literal> and
+    <literal>BY TARGET</literal> qualifiers to
+    <literal>WHEN NOT MATCHED</literal>, and
+    <literal>DO NOTHING</literal> action are extensions to
     the <acronym>SQL</acronym> standard.
   </para>
  </refsect1>
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index eb8a87f..a1add5a
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -902,8 +902,16 @@ ExecInitPartitionInfo(ModifyTableState *
 			action_state = makeNode(MergeActionState);
 			action_state->mas_action = action;
 
-			/* And put the action in the appropriate list */
-			if (action->matched)
+			/*
+			 * Put the action in the appropriate list.
+			 *
+			 * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+			 * actions in exactly the same way as WHEN MATCHED actions, since
+			 * they both match the target (see ExecMerge).  Thus both types go
+			 * in the "matched" list.  Only WHEN NOT MATCHED BY TARGET actions
+			 * go in the "not matched" list.
+			 */
+			if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
 				list = &leaf_part_rri->ri_matchedMergeAction;
 			else
 				list = &leaf_part_rri->ri_notMatchedMergeAction;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 2a5fec8..314dbaf
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -24,13 +24,15 @@
  *		values plus row-locating info for UPDATE and MERGE cases, or just the
  *		row-locating info for DELETE cases.
  *
- *		MERGE runs a join between the source relation and the target
- *		table; if any WHEN NOT MATCHED clauses are present, then the
- *		join is an outer join.  In this case, any unmatched tuples will
- *		have NULL row-locating info, and only INSERT can be run. But for
- *		matched tuples, then row-locating info is used to determine the
- *		tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED,
- *		then an inner join is used, so all tuples contain row-locating info.
+ *		MERGE runs a join between the source relation and the target table.
+ *		If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join
+ *		is an outer join that might output tuples without a matching target
+ *		tuple.  In this case, any unmatched target tuples will have NULL
+ *		row-locating info, and only INSERT can be run.  But for matched
+ *		target tuples, the row-locating info is used to determine the tuple
+ *		to UPDATE or DELETE.  When all clauses are WHEN MATCHED or WHEN NOT
+ *		MATCHED BY SOURCE, all tuples produced by the join will include a
+ *		matching target tuple, so all tuples contain row-locating info.
  *
  *		If the query specifies RETURNING, then the ModifyTable returns a
  *		RETURNING tuple after completing each row insert, update, or delete.
@@ -2715,6 +2717,17 @@ ExecMerge(ModifyTableContext *context, R
 	bool		matched;
 
 	/*-----
+	 * Note that as far as the executor is concerned, WHEN NOT MATCHED BY
+	 * SOURCE actions are treated exactly the same as WHEN MATCHED actions,
+	 * since both match target tuples.  They are distinguished from one
+	 * another by a qual that tests if the source tuple is NULL, but the
+	 * executor knows nothing about the contents of the merge action quals.
+	 * Thus WHEN MATCHED and WHEN NOT MATCHED BY SOURCE actions are stored
+	 * together in the same "matched" list.  Thus, in the dicussion that
+	 * follows "MATCHED" means "matched by target", and should be taken to
+	 * include both WHEN MATCHED and WHEN NOT MATCHED BY SOURCE, while "NOT
+	 * MATCHED" means NOT MATCHED BY TARGET.
+	 *
 	 * If we are dealing with a WHEN MATCHED case (tupleid is valid), we
 	 * execute the first action for which the additional WHEN MATCHED AND
 	 * quals pass.  If an action without quals is found, that action is
@@ -3044,9 +3057,13 @@ lmerge_matched:
 							 * is executed.
 							 *
 							 * Update tupleid to that of the new tuple, for
-							 * the refetch we do at the top.
+							 * the refetch we do at the top, and install the
+							 * updated plan slot in ecxt_innertuple in case
+							 * the source tuple is now NULL (a change from
+							 * MATCHED to NOT MATCHED BY SOURCE).
 							 */
 							ItemPointerCopy(&context->tmfd.ctid, tupleid);
+							econtext->ecxt_innertuple = epqslot;
 							goto lmerge_matched;
 
 						case TM_Deleted:
@@ -3251,8 +3268,14 @@ ExecInitMerge(ModifyTableState *mtstate,
 			 * We create two lists - one for WHEN MATCHED actions and one for
 			 * WHEN NOT MATCHED actions - and stick the MergeActionState into
 			 * the appropriate list.
+			 *
+			 * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+			 * actions in exactly the same way as WHEN MATCHED actions, since
+			 * they both match the target (see ExecMerge).  Thus both types go
+			 * in the "matched" list.  Only WHEN NOT MATCHED BY TARGET actions
+			 * go in the "not matched" list.
 			 */
-			if (action_state->mas_action->matched)
+			if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
 				list = &resultRelInfo->ri_matchedMergeAction;
 			else
 				list = &resultRelInfo->ri_notMatchedMergeAction;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 73ff407..be45646
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -153,6 +153,9 @@ transform_MERGE_to_join(Query *parse)
 {
 	RangeTblEntry *joinrte;
 	JoinExpr   *joinexpr;
+	ListCell   *lc;
+	bool		src_only_tuples;
+	bool		tgt_only_tuples;
 	JoinType	jointype;
 	int			joinrti;
 	List	   *vars;
@@ -164,12 +167,32 @@ transform_MERGE_to_join(Query *parse)
 	vars = NIL;
 
 	/*
-	 * When any WHEN NOT MATCHED THEN INSERT clauses exist, we need to use an
-	 * outer join so that we process all unmatched tuples from the source
-	 * relation.  If none exist, we can use an inner join.
+	 * Work out what kind of join is required.  If there any WHEN NOT MATCHED
+	 * BY SOURCE/TARGET actions, an outer join is required so that we process
+	 * all unmatched tuples from the source and/or target relations.
+	 * Otherwise, we can use an inner join.
 	 */
-	if (parse->mergeUseOuterJoin)
+	src_only_tuples = false;
+	tgt_only_tuples = false;
+	foreach(lc, parse->mergeActionList)
+	{
+		MergeAction *action = lfirst_node(MergeAction, lc);
+
+		if (action->commandType != CMD_NOTHING)
+		{
+			if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+				tgt_only_tuples = true;
+			if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET)
+				src_only_tuples = true;
+		}
+	}
+
+	if (src_only_tuples && tgt_only_tuples)
+		jointype = JOIN_FULL;
+	else if (src_only_tuples)
 		jointype = JOIN_RIGHT;
+	else if (tgt_only_tuples)
+		jointype = JOIN_LEFT;
 	else
 		jointype = JOIN_INNER;
 
@@ -215,6 +238,51 @@ transform_MERGE_to_join(Query *parse)
 	/* Make the new join be the sole entry in the query's jointree */
 	parse->jointree->fromlist = list_make1(joinexpr);
 	parse->jointree->quals = NULL;
+
+	/*
+	 * If there any WHEN NOT MATCHED BY SOURCE actions that require unmatched
+	 * tuples from the target relation to be processed, add additional WHEN
+	 * conditions to every action to check whether tuples from the source
+	 * match or not, as necessary.
+	 *
+	 * This distinguishes WHEN NOT MATCHED BY SOURCE actions (identified by a
+	 * "source IS NOT DISTINCT FROM NULL" clause) from WHEN MATCHED actions
+	 * (identified by a "source IS DISTINCT FROM NULL" clause).
+	 *
+	 * Additionally, a "source IS DISTINCT FROM NULL" clause is required for
+	 * WHEN NOT MATCHED [BY TARGET] actions in case the executor attempts to
+	 * invoke such an action for a concurrently deleted target row that ends
+	 * up matching neither source nor target.
+	 */
+	if (tgt_only_tuples)
+	{
+		foreach(lc, parse->mergeActionList)
+		{
+			MergeAction *action = lfirst_node(MergeAction, lc);
+			bool		src_null;
+			Var		   *var;
+			NullTest   *ntest;
+
+			/* Make a "source IS [NOT] DISTINCT FROM NULL" clause */
+			src_null = action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE;
+
+			var = makeWholeRowVar(rt_fetch(parse->mergeSourceRelation,
+										   parse->rtable),
+								  parse->mergeSourceRelation, 0, false);
+
+			/* source wholerow Var is nullable by the new join */
+			var->varnullingrels = bms_make_singleton(joinrti);
+
+			ntest = makeNode(NullTest);
+			ntest->arg = (Expr *) var;
+			ntest->nulltesttype = src_null ? IS_NULL : IS_NOT_NULL;
+			ntest->argisrow = false;
+			ntest->location = -1;
+
+			/* Combine it with the action's WHEN condition */
+			action->qual = make_and_qual((Node *) ntest, action->qual);
+		}
+	}
 }
 
 /*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 39ab7ea..2f152f0
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -275,6 +275,7 @@ static Node *makeRecursiveViewSelect(cha
 	struct SelectLimit *selectlimit;
 	SetQuantifier setquantifier;
 	struct GroupClause *groupclause;
+	MergeMatchKind mergematch;
 	MergeWhenClause *mergewhen;
 	struct KeyActions *keyactions;
 	struct KeyAction *keyaction;
@@ -514,6 +515,7 @@ static Node *makeRecursiveViewSelect(cha
 %type <onconflict> opt_on_conflict
 %type <mergewhen>	merge_insert merge_update merge_delete
 
+%type <mergematch> merge_when_tgt_matched merge_when_tgt_not_matched
 %type <node>	merge_when_clause opt_merge_when_condition
 %type <list>	merge_when_list
 
@@ -760,11 +762,11 @@ static Node *makeRecursiveViewSelect(cha
 	SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
 	SEQUENCE SEQUENCES
 	SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
-	SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
+	SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SOURCE SQL_P STABLE STANDALONE_P
 	START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
 	SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
 
-	TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
+	TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
 	TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
 	TREAT TRIGGER TRIM TRUE_P
 	TRUNCATE TRUSTED TYPE_P TYPES_P
@@ -12267,50 +12269,66 @@ merge_when_list:
 			| merge_when_list merge_when_clause		{ $$ = lappend($1,$2); }
 		;
 
+/*
+ * A WHEN clause may be WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT
+ * MATCHED [BY TARGET]. The first two cases match target tuples, and support
+ * UPDATE/DELETE/DO NOTHING actions. The third case does not match target
+ * tuples, and only supports INSERT/DO NOTHING actions.
+ */
 merge_when_clause:
-			WHEN MATCHED opt_merge_when_condition THEN merge_update
+			merge_when_tgt_matched opt_merge_when_condition THEN merge_update
 				{
-					$5->matched = true;
-					$5->condition = $3;
+					$4->matchKind = $1;
+					$4->condition = $2;
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
-			| WHEN MATCHED opt_merge_when_condition THEN merge_delete
+			| merge_when_tgt_matched opt_merge_when_condition THEN merge_delete
 				{
-					$5->matched = true;
-					$5->condition = $3;
+					$4->matchKind = $1;
+					$4->condition = $2;
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
-			| WHEN NOT MATCHED opt_merge_when_condition THEN merge_insert
+			| merge_when_tgt_not_matched opt_merge_when_condition THEN merge_insert
 				{
-					$6->matched = false;
-					$6->condition = $4;
+					$4->matchKind = $1;
+					$4->condition = $2;
 
-					$$ = (Node *) $6;
+					$$ = (Node *) $4;
 				}
-			| WHEN MATCHED opt_merge_when_condition THEN DO NOTHING
+			| merge_when_tgt_matched opt_merge_when_condition THEN DO NOTHING
 				{
 					MergeWhenClause *m = makeNode(MergeWhenClause);
 
-					m->matched = true;
+					m->matchKind = $1;
 					m->commandType = CMD_NOTHING;
-					m->condition = $3;
+					m->condition = $2;
 
 					$$ = (Node *) m;
 				}
-			| WHEN NOT MATCHED opt_merge_when_condition THEN DO NOTHING
+			| merge_when_tgt_not_matched opt_merge_when_condition THEN DO NOTHING
 				{
 					MergeWhenClause *m = makeNode(MergeWhenClause);
 
-					m->matched = false;
+					m->matchKind = $1;
 					m->commandType = CMD_NOTHING;
-					m->condition = $4;
+					m->condition = $2;
 
 					$$ = (Node *) m;
 				}
 		;
 
+merge_when_tgt_matched:
+			WHEN MATCHED					{ $$ = MERGE_WHEN_MATCHED; }
+			| WHEN NOT MATCHED BY SOURCE	{ $$ = MERGE_WHEN_NOT_MATCHED_BY_SOURCE; }
+		;
+
+merge_when_tgt_not_matched:
+			WHEN NOT MATCHED				{ $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; }
+			| WHEN NOT MATCHED BY TARGET	{ $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; }
+		;
+
 opt_merge_when_condition:
 			AND a_expr				{ $$ = $2; }
 			|						{ $$ = NULL; }
@@ -17192,6 +17210,7 @@ unreserved_keyword:
 			| SIMPLE
 			| SKIP
 			| SNAPSHOT
+			| SOURCE
 			| SQL_P
 			| STABLE
 			| STANDALONE_P
@@ -17210,6 +17229,7 @@ unreserved_keyword:
 			| SYSTEM_P
 			| TABLES
 			| TABLESPACE
+			| TARGET
 			| TEMP
 			| TEMPLATE
 			| TEMPORARY
@@ -17800,6 +17820,7 @@ bare_label_keyword:
 			| SMALLINT
 			| SNAPSHOT
 			| SOME
+			| SOURCE
 			| SQL_P
 			| STABLE
 			| STANDALONE_P
@@ -17823,6 +17844,7 @@ bare_label_keyword:
 			| TABLES
 			| TABLESAMPLE
 			| TABLESPACE
+			| TARGET
 			| TEMP
 			| TEMPLATE
 			| TEMPORARY
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
new file mode 100644
index 91b1156..728b533
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -43,9 +43,9 @@ static void setNamespaceVisibilityForRTE
  * Make appropriate changes to the namespace visibility while transforming
  * individual action's quals and targetlist expressions. In particular, for
  * INSERT actions we must only see the source relation (since INSERT action is
- * invoked for NOT MATCHED tuples and hence there is no target tuple to deal
- * with). On the other hand, UPDATE and DELETE actions can see both source and
- * target relations.
+ * invoked for NOT MATCHED [BY TARGET] tuples and hence there is no target
+ * tuple to deal with). On the other hand, UPDATE and DELETE actions can see
+ * both source and target relations, unless invoked for NOT MATCHED BY SOURCE.
  *
  * Also, since the internal join node can hide the source and target
  * relations, we must explicitly make the respective relation as visible so
@@ -61,7 +61,7 @@ setNamespaceForMergeWhen(ParseState *pst
 	targetRelRTE = rt_fetch(targetRTI, pstate->p_rtable);
 	sourceRelRTE = rt_fetch(sourceRTI, pstate->p_rtable);
 
-	if (mergeWhenClause->matched)
+	if (mergeWhenClause->matchKind == MERGE_WHEN_MATCHED)
 	{
 		Assert(mergeWhenClause->commandType == CMD_UPDATE ||
 			   mergeWhenClause->commandType == CMD_DELETE ||
@@ -73,11 +73,25 @@ setNamespaceForMergeWhen(ParseState *pst
 		setNamespaceVisibilityForRTE(pstate->p_namespace,
 									 sourceRelRTE, true, true);
 	}
-	else
+	else if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
 	{
 		/*
-		 * NOT MATCHED actions can't see target relation, but they can see
-		 * source relation.
+		 * NOT MATCHED BY SOURCE actions can see the target relation, but they
+		 * can't see the source relation.
+		 */
+		Assert(mergeWhenClause->commandType == CMD_UPDATE ||
+			   mergeWhenClause->commandType == CMD_DELETE ||
+			   mergeWhenClause->commandType == CMD_NOTHING);
+		setNamespaceVisibilityForRTE(pstate->p_namespace,
+									 targetRelRTE, true, true);
+		setNamespaceVisibilityForRTE(pstate->p_namespace,
+									 sourceRelRTE, false, false);
+	}
+	else						/* MERGE_WHEN_NOT_MATCHED_BY_TARGET */
+	{
+		/*
+		 * NOT MATCHED [BY TARGET] actions can't see target relation, but they
+		 * can see source relation.
 		 */
 		Assert(mergeWhenClause->commandType == CMD_INSERT ||
 			   mergeWhenClause->commandType == CMD_NOTHING);
@@ -98,8 +112,7 @@ transformMergeStmt(ParseState *pstate, M
 	Query	   *qry = makeNode(Query);
 	ListCell   *l;
 	AclMode		targetPerms = ACL_NO_RIGHTS;
-	bool		is_terminal[2];
-	Index		sourceRTI;
+	bool		is_terminal[3];
 	List	   *mergeActionList;
 	Node	   *joinExpr;
 	ParseNamespaceItem *nsitem;
@@ -125,12 +138,12 @@ transformMergeStmt(ParseState *pstate, M
 	/*
 	 * Check WHEN clauses for permissions and sanity
 	 */
-	is_terminal[0] = false;
-	is_terminal[1] = false;
+	is_terminal[MERGE_WHEN_MATCHED] = false;
+	is_terminal[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = false;
+	is_terminal[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = false;
 	foreach(l, stmt->mergeWhenClauses)
 	{
 		MergeWhenClause *mergeWhenClause = (MergeWhenClause *) lfirst(l);
-		int			when_type = (mergeWhenClause->matched ? 0 : 1);
 
 		/*
 		 * Collect action types so we can check target permissions
@@ -155,12 +168,12 @@ transformMergeStmt(ParseState *pstate, M
 		/*
 		 * Check for unreachable WHEN clauses
 		 */
-		if (is_terminal[when_type])
+		if (is_terminal[mergeWhenClause->matchKind])
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
 					 errmsg("unreachable WHEN clause specified after unconditional WHEN clause")));
 		if (mergeWhenClause->condition == NULL)
-			is_terminal[when_type] = true;
+			is_terminal[mergeWhenClause->matchKind] = true;
 	}
 
 	/*
@@ -193,8 +206,8 @@ transformMergeStmt(ParseState *pstate, M
 	/* Now transform the source relation to produce the source RTE. */
 	transformFromClause(pstate,
 						list_make1(stmt->sourceRelation));
-	sourceRTI = list_length(pstate->p_rtable);
-	nsitem = GetNSItemByRangeTablePosn(pstate, sourceRTI, 0);
+	qry->mergeSourceRelation = list_length(pstate->p_rtable);
+	nsitem = GetNSItemByRangeTablePosn(pstate, qry->mergeSourceRelation, 0);
 
 	/*
 	 * Check that the target table doesn't conflict with the source table.
@@ -255,11 +268,7 @@ transformMergeStmt(ParseState *pstate, M
 
 		action = makeNode(MergeAction);
 		action->commandType = mergeWhenClause->commandType;
-		action->matched = mergeWhenClause->matched;
-
-		/* Use an outer join if any INSERT actions exist in the command. */
-		if (action->commandType == CMD_INSERT)
-			qry->mergeUseOuterJoin = true;
+		action->matchKind = mergeWhenClause->matchKind;
 
 		/*
 		 * Set namespace for the specific action. This must be done before
@@ -267,7 +276,7 @@ transformMergeStmt(ParseState *pstate, M
 		 */
 		setNamespaceForMergeWhen(pstate, mergeWhenClause,
 								 qry->resultRelation,
-								 sourceRTI);
+								 qry->mergeSourceRelation);
 
 		/*
 		 * Transform the WHEN condition.
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
new file mode 100644
index 32bd2f1..fcf78c4
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -496,9 +496,10 @@ OffsetVarNodes(Node *node, int offset, i
 		/*
 		 * If we are starting at a Query, and sublevels_up is zero, then we
 		 * must also fix rangetable indexes in the Query itself --- namely
-		 * resultRelation, exclRelIndex and rowMarks entries.  sublevels_up
-		 * cannot be zero when recursing into a subquery, so there's no need
-		 * to have the same logic inside OffsetVarNodes_walker.
+		 * resultRelation, mergeSourceRelation, exclRelIndex and rowMarks
+		 * entries.  sublevels_up cannot be zero when recursing into a
+		 * subquery, so there's no need to have the same logic inside
+		 * OffsetVarNodes_walker.
 		 */
 		if (sublevels_up == 0)
 		{
@@ -507,6 +508,9 @@ OffsetVarNodes(Node *node, int offset, i
 			if (qry->resultRelation)
 				qry->resultRelation += offset;
 
+			if (qry->mergeSourceRelation)
+				qry->mergeSourceRelation += offset;
+
 			if (qry->onConflict && qry->onConflict->exclRelIndex)
 				qry->onConflict->exclRelIndex += offset;
 
@@ -687,9 +691,10 @@ ChangeVarNodes(Node *node, int rt_index,
 		/*
 		 * If we are starting at a Query, and sublevels_up is zero, then we
 		 * must also fix rangetable indexes in the Query itself --- namely
-		 * resultRelation and rowMarks entries.  sublevels_up cannot be zero
-		 * when recursing into a subquery, so there's no need to have the same
-		 * logic inside ChangeVarNodes_walker.
+		 * resultRelation, mergeSourceRelation, exclRelIndex and rowMarks
+		 * entries.  sublevels_up cannot be zero when recursing into a
+		 * subquery, so there's no need to have the same logic inside
+		 * ChangeVarNodes_walker.
 		 */
 		if (sublevels_up == 0)
 		{
@@ -698,6 +703,9 @@ ChangeVarNodes(Node *node, int rt_index,
 			if (qry->resultRelation == rt_index)
 				qry->resultRelation = new_index;
 
+			if (qry->mergeSourceRelation == rt_index)
+				qry->mergeSourceRelation = new_index;
+
 			/* this is unlikely to ever be used, but ... */
 			if (qry->onConflict && qry->onConflict->exclRelIndex == rt_index)
 				qry->onConflict->exclRelIndex = new_index;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index d3a973d..1d78714
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7077,6 +7077,7 @@ get_merge_query_def(Query *query, depars
 	StringInfo	buf = context->buf;
 	RangeTblEntry *rte;
 	ListCell   *lc;
+	bool		haveNotMatchedBySource;
 
 	/* Insert the WITH clause if given */
 	get_with_clause(query, context);
@@ -7104,6 +7105,25 @@ get_merge_query_def(Query *query, depars
 						 -PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
 	get_rule_expr(query->jointree->quals, context, false);
 
+	/*
+	 * Test for any NOT MATCHED BY SOURCE actions.  If there are none, then
+	 * any NOT MATCHED BY TARGET actions are output as "WHEN NOT MATCHED", per
+	 * SQL standard.  Otherwise, we have a non-SQL-standard query, so output
+	 * "BY SOURCE" / "BY TARGET" qualifiers for all NOT MATCHED actions, to be
+	 * more explicit.
+	 */
+	haveNotMatchedBySource = false;
+	foreach(lc, query->mergeActionList)
+	{
+		MergeAction *action = lfirst_node(MergeAction, lc);
+
+		if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+		{
+			haveNotMatchedBySource = true;
+			break;
+		}
+	}
+
 	/* Print each merge action */
 	foreach(lc, query->mergeActionList)
 	{
@@ -7111,7 +7131,24 @@ get_merge_query_def(Query *query, depars
 
 		appendContextKeyword(context, " WHEN ",
 							 -PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
-		appendStringInfo(buf, "%sMATCHED", action->matched ? "" : "NOT ");
+		switch (action->matchKind)
+		{
+			case MERGE_WHEN_MATCHED:
+				appendStringInfo(buf, "MATCHED");
+				break;
+			case MERGE_WHEN_NOT_MATCHED_BY_SOURCE:
+				appendStringInfo(buf, "NOT MATCHED BY SOURCE");
+				break;
+			case MERGE_WHEN_NOT_MATCHED_BY_TARGET:
+				if (haveNotMatchedBySource)
+					appendStringInfo(buf, "NOT MATCHED BY TARGET");
+				else
+					appendStringInfo(buf, "NOT MATCHED");
+				break;
+			default:
+				elog(ERROR, "unrecognized matchKind: %d",
+					 (int) action->matchKind);
+		}
 
 		if (action->qual)
 		{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 935bb9b..6cfff24
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4270,17 +4270,35 @@ psql_completion(const char *text, int st
 			 TailMatches("USING", MatchAny, MatchAny, "ON", MatchAny, MatchAny, MatchAny, "WHEN"))
 		COMPLETE_WITH("MATCHED", "NOT MATCHED");
 
-	/* Complete ... WHEN [NOT] MATCHED with THEN/AND */
+	/*
+	 * Complete ... WHEN MATCHED and WHEN NOT MATCHED BY SOURCE|TARGET with
+	 * THEN/AND
+	 */
 	else if (TailMatches("WHEN", "MATCHED") ||
-			 TailMatches("WHEN", "NOT", "MATCHED"))
+			 TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE|TARGET"))
 		COMPLETE_WITH("THEN", "AND");
 
-	/* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO NOTHING */
-	else if (TailMatches("WHEN", "MATCHED", "THEN"))
+	/* Complete ... WHEN NOT MATCHED with BY/THEN/AND */
+	else if (TailMatches("WHEN", "NOT", "MATCHED"))
+		COMPLETE_WITH("BY", "THEN", "AND");
+
+	/* Complete ... WHEN NOT MATCHED BY with SOURCE/TARGET */
+	else if (TailMatches("WHEN", "NOT", "MATCHED", "BY"))
+		COMPLETE_WITH("SOURCE", "TARGET");
+
+	/*
+	 * Complete ... WHEN MATCHED THEN and WHEN NOT MATCHED BY SOURCE THEN with
+	 * UPDATE SET/DELETE/DO NOTHING
+	 */
+	else if (TailMatches("WHEN", "MATCHED", "THEN") ||
+			 TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE", "THEN"))
 		COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING");
 
-	/* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */
-	else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN"))
+	/*
+	 * Complete ... WHEN NOT MATCHED [BY TARGET] THEN with INSERT/DO NOTHING
+	 */
+	else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN") ||
+			 TailMatches("WHEN", "NOT", "MATCHED", "BY", "TARGET", "THEN"))
 		COMPLETE_WITH("INSERT", "DO NOTHING");
 
 /* NOTIFY --- can be inside EXPLAIN, RULE, etc */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index b3bec90..65c3e55
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -183,8 +183,9 @@ typedef struct Query
 								 * also USING clause for MERGE */
 
 	List	   *mergeActionList;	/* list of actions for MERGE (only) */
-	/* whether to use outer join */
-	bool		mergeUseOuterJoin pg_node_attr(query_jumble_ignore);
+
+	/* rtable index of source relation for MERGE */
+	int			mergeSourceRelation pg_node_attr(query_jumble_ignore);
 
 	List	   *targetList;		/* target list (of TargetEntry) */
 
@@ -1662,6 +1663,14 @@ typedef struct CommonTableExpr
 	 ((Query *) (cte)->ctequery)->targetList : \
 	 ((Query *) (cte)->ctequery)->returningList)
 
+/* MATCHED/NOT MATCHED specification for WHEN clauses in a MERGE statement */
+typedef enum MergeMatchKind
+{
+	MERGE_WHEN_MATCHED,
+	MERGE_WHEN_NOT_MATCHED_BY_SOURCE,
+	MERGE_WHEN_NOT_MATCHED_BY_TARGET
+} MergeMatchKind;
+
 /*
  * MergeWhenClause -
  *		raw parser representation of a WHEN clause in a MERGE statement
@@ -1671,7 +1680,7 @@ typedef struct CommonTableExpr
 typedef struct MergeWhenClause
 {
 	NodeTag		type;
-	bool		matched;		/* true=MATCHED, false=NOT MATCHED */
+	MergeMatchKind matchKind;	/* MATCHED/NOT MATCHED BY SOURCE/TARGET */
 	CmdType		commandType;	/* INSERT/UPDATE/DELETE/DO NOTHING */
 	OverridingKind override;	/* OVERRIDING clause */
 	Node	   *condition;		/* WHEN conditions (raw parser) */
@@ -1687,7 +1696,7 @@ typedef struct MergeWhenClause
 typedef struct MergeAction
 {
 	NodeTag		type;
-	bool		matched;		/* true=MATCHED, false=NOT MATCHED */
+	MergeMatchKind matchKind;	/* MATCHED/NOT MATCHED BY SOURCE/TARGET */
 	CmdType		commandType;	/* INSERT/UPDATE/DELETE/DO NOTHING */
 	/* OVERRIDING clause */
 	OverridingKind override pg_node_attr(query_jumble_ignore);
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
new file mode 100644
index f5b2e61..c34635d
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -401,6 +401,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
 PG_KEYWORD("smallint", SMALLINT, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("snapshot", SNAPSHOT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("some", SOME, RESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("source", SOURCE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("sql", SQL_P, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("stable", STABLE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("standalone", STANDALONE_P, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -424,6 +425,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW
 PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("target", TARGET, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 133d421..0c35a35
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -71,6 +71,15 @@ WHEN MATCHED THEN
 ERROR:  syntax error at or near "INSERT"
 LINE 5:  INSERT DEFAULT VALUES;
          ^
+-- NOT MATCHED BY SOURCE/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	INSERT DEFAULT VALUES;
+ERROR:  syntax error at or near "INSERT"
+LINE 5:  INSERT DEFAULT VALUES;
+         ^
 -- incorrectly specifying INTO target
 MERGE INTO target t
 USING source AS s
@@ -107,6 +116,15 @@ WHEN NOT MATCHED THEN
 ERROR:  syntax error at or near "UPDATE"
 LINE 5:  UPDATE SET balance = 0;
          ^
+-- NOT MATCHED BY TARGET/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY TARGET THEN
+	UPDATE SET balance = 0;
+ERROR:  syntax error at or near "UPDATE"
+LINE 5:  UPDATE SET balance = 0;
+         ^
 -- UPDATE tablename
 MERGE INTO target t
 USING source AS s
@@ -271,6 +289,22 @@ SELECT * FROM target ORDER BY tid;
 (4 rows)
 
 ROLLBACK;
+-- DELETE/INSERT not matched by source/target
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	DELETE
+WHEN NOT MATCHED BY TARGET THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   4 |      40
+(1 row)
+
+ROLLBACK;
 -- index plans
 INSERT INTO target SELECT generate_series(1000,2500), 0;
 ALTER TABLE target ADD PRIMARY KEY (tid);
@@ -738,6 +772,19 @@ SELECT * FROM wq_target;
    1 |      -1
 (1 row)
 
+-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+	DELETE;
+ERROR:  invalid reference to FROM-clause entry for table "s"
+LINE 3: WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+                                       ^
+DETAIL:  There is an entry for table "s", but it cannot be referenced from this part of the query.
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN
+    DELETE;
 -- conditions in MATCHED clause can refer to both source and target
 SELECT * FROM wq_source;
  balance | sid 
@@ -923,6 +970,45 @@ SELECT * FROM target ORDER BY tid;
 (3 rows)
 
 ROLLBACK;
+-- UPSERT with UPDATE/DELETE when not matched by source
+BEGIN;
+DELETE FROM SOURCE WHERE sid = 2;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND t.balance > s.delta THEN
+    UPDATE SET balance = t.balance - s.delta
+WHEN MATCHED THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+    INSERT VALUES (s.sid, s.delta)
+WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED BY SOURCE THEN
+	DELETE;
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE DELETE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  BEFORE DELETE ROW trigger row: (2,20)
+NOTICE:  BEFORE UPDATE ROW trigger row: (1,10) -> (1,0)
+NOTICE:  AFTER UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE:  AFTER INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER DELETE ROW trigger row: (2,20)
+NOTICE:  AFTER UPDATE ROW trigger row: (1,10) -> (1,0)
+NOTICE:  AFTER DELETE STATEMENT trigger
+NOTICE:  AFTER UPDATE STATEMENT trigger
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |       0
+   3 |      10
+   4 |      40
+(3 rows)
+
+ROLLBACK;
 -- Test behavior of triggers that turn UPDATE/DELETE into no-ops
 create or replace function skip_merge_op() returns trigger
 language plpgsql as
@@ -1451,6 +1537,50 @@ WHEN NOT MATCHED AND s.a < 20 THEN
                ->  Seq Scan on ex_mtarget t (actual rows=49 loops=1)
 (12 rows)
 
+-- not matched by source
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN
+	DELETE');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples: skipped=54
+   ->  Merge Left Join (actual rows=54 loops=1)
+         Merge Cond: (t.a = s.a)
+         ->  Sort (actual rows=54 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=54 loops=1)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+(12 rows)
+
+-- not matched by source and target
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN
+	DELETE
+WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN
+	INSERT VALUES (a, b)');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples: skipped=100
+   ->  Merge Full Join (actual rows=100 loops=1)
+         Merge Cond: (t.a = s.a)
+         ->  Sort (actual rows=54 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=54 loops=1)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+(12 rows)
+
 -- nothing
 SELECT explain_merge('
 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
new file mode 100644
index 7fd81e6..478e714
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3670,7 +3670,39 @@ BEGIN ATOMIC
      THEN INSERT (filling[1], id)
       VALUES (s.a, s.a);
 END
+CREATE FUNCTION merge_sf_test2()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ MERGE INTO sf_target t
+   USING rule_merge1 s
+   ON (s.a = t.id)
+WHEN NOT MATCHED
+   THEN INSERT (data, id)
+   VALUES (s.a, s.a)
+WHEN MATCHED
+   THEN UPDATE SET data = s.b
+WHEN NOT MATCHED BY SOURCE
+   THEN DELETE;
+END;
+\sf merge_sf_test2
+CREATE OR REPLACE FUNCTION public.merge_sf_test2()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ MERGE INTO sf_target t
+    USING rule_merge1 s
+    ON (s.a = t.id)
+    WHEN NOT MATCHED BY TARGET
+     THEN INSERT (data, id)
+      VALUES (s.a, s.a)
+    WHEN MATCHED
+     THEN UPDATE SET data = s.b
+    WHEN NOT MATCHED BY SOURCE
+     THEN DELETE;
+END
 DROP FUNCTION merge_sf_test;
+DROP FUNCTION merge_sf_test2;
 DROP TABLE sf_target;
 --
 -- Test enabling/disabling
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 4cf6db9..bd399ab
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -51,6 +51,12 @@ USING source AS s
 ON t.tid = s.sid
 WHEN MATCHED THEN
 	INSERT DEFAULT VALUES;
+-- NOT MATCHED BY SOURCE/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	INSERT DEFAULT VALUES;
 -- incorrectly specifying INTO target
 MERGE INTO target t
 USING source AS s
@@ -75,6 +81,12 @@ USING source AS s
 ON t.tid = s.sid
 WHEN NOT MATCHED THEN
 	UPDATE SET balance = 0;
+-- NOT MATCHED BY TARGET/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY TARGET THEN
+	UPDATE SET balance = 0;
 -- UPDATE tablename
 MERGE INTO target t
 USING source AS s
@@ -213,6 +225,18 @@ WHEN NOT MATCHED THEN
 SELECT * FROM target ORDER BY tid;
 ROLLBACK;
 
+-- DELETE/INSERT not matched by source/target
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	DELETE
+WHEN NOT MATCHED BY TARGET THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
 -- index plans
 INSERT INTO target SELECT generate_series(1000,2500), 0;
 ALTER TABLE target ADD PRIMARY KEY (tid);
@@ -497,6 +521,17 @@ WHEN NOT MATCHED AND s.balance = 100 THE
 	INSERT (tid) VALUES (s.sid);
 SELECT * FROM wq_target;
 
+-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+	DELETE;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN
+    DELETE;
+
 -- conditions in MATCHED clause can refer to both source and target
 SELECT * FROM wq_source;
 MERGE INTO wq_target t
@@ -624,6 +659,25 @@ WHEN NOT MATCHED THEN
 SELECT * FROM target ORDER BY tid;
 ROLLBACK;
 
+-- UPSERT with UPDATE/DELETE when not matched by source
+BEGIN;
+DELETE FROM SOURCE WHERE sid = 2;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND t.balance > s.delta THEN
+    UPDATE SET balance = t.balance - s.delta
+WHEN MATCHED THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+    INSERT VALUES (s.sid, s.delta)
+WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED BY SOURCE THEN
+	DELETE;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
 -- Test behavior of triggers that turn UPDATE/DELETE into no-ops
 create or replace function skip_merge_op() returns trigger
 language plpgsql as
@@ -929,6 +983,20 @@ WHEN MATCHED AND t.a >= 30 AND t.a <= 40
 WHEN NOT MATCHED AND s.a < 20 THEN
 	INSERT VALUES (a, b)');
 
+-- not matched by source
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN
+	DELETE');
+
+-- not matched by source and target
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN
+	DELETE
+WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN
+	INSERT VALUES (a, b)');
+
 -- nothing
 SELECT explain_merge('
 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
new file mode 100644
index 8b7e255..ae9141f
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1323,7 +1323,26 @@ END;
 
 \sf merge_sf_test
 
+CREATE FUNCTION merge_sf_test2()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ MERGE INTO sf_target t
+   USING rule_merge1 s
+   ON (s.a = t.id)
+WHEN NOT MATCHED
+   THEN INSERT (data, id)
+   VALUES (s.a, s.a)
+WHEN MATCHED
+   THEN UPDATE SET data = s.b
+WHEN NOT MATCHED BY SOURCE
+   THEN DELETE;
+END;
+
+\sf merge_sf_test2
+
 DROP FUNCTION merge_sf_test;
+DROP FUNCTION merge_sf_test2;
 DROP TABLE sf_target;
 
 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index 2608547..303387c
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1548,6 +1548,7 @@ MergeAppendState
 MergeJoin
 MergeJoinClause
 MergeJoinState
+MergeMatchKind
 MergePath
 MergeScanSelCache
 MergeStmt

---

From: Peter Smith <[email-masked]>
Date: Mon, 22 Jan 2024 13:10:09 +1100

Hi, this patch was marked in CF as "Needs Review" [1], but there has
been no activity on this thread for 6+ months.

Is anything else planned? Can you post something to elicit more
interest in the latest patch? Otherwise, if nothing happens then the
CF entry will be closed ("Returned with feedback") at the end of this
CF.

======
[1]  https://commitfest.postgresql.org/46/4092/

Kind Regards,
Peter Smith.

---

From: vignesh C <[email-masked]>
Date: Fri, 26 Jan 2024 20:29:20 +0530

> and "BY TARGET" qualifiers for all not-matched actions, to make the

CFBot shows that the patch does not apply anymore as in [1]:
=== Applying patches on top of PostgreSQL commit ID
f2bf8fb04886e3ea82e7f7f86696ac78e06b7e60 ===
=== applying patch ./support-merge-when-not-matched-by-source-v8.patch
...
patching file doc/src/sgml/ref/merge.sgml
Hunk #5 FAILED at 409.
Hunk #9 FAILED at 673.
2 out of 9 hunks FAILED -- saving rejects to file
doc/src/sgml/ref/merge.sgml.rej
..
patching file src/include/nodes/parsenodes.h
Hunk #1 succeeded at 175 (offset -8 lines).
Hunk #2 succeeded at 1657 (offset -6 lines).
Hunk #3 succeeded at 1674 (offset -6 lines).
Hunk #4 FAILED at 1696.
1 out of 4 hunks FAILED -- saving rejects to file
src/include/nodes/parsenodes.h.rej

Please post an updated version for the same.

[1] - http://cfbot.cputube.org/patch_46_4092.log

Regards,
Vignesh

---

From: Dean Rasheed <[email-masked]>
Date: Fri, 26 Jan 2024 15:48:31 +0000

> CF entry will be closed ("Returned with feedback") at the end of this

I think it has had a decent amount of review and all the review
comments have been addressed. I'm not quite sure from Alvaro's last
comment whether he was implying that he thought it was ready for
commit.

Looking back through the thread, the general sentiment seems to be in
favour of adding this feature, and I still think it's worth doing, but
I haven't managed to find much time to progress it recently.

Regards,
Dean

---

From: Dean Rasheed <[email-masked]>
Date: Fri, 26 Jan 2024 15:48:44 +0000

> CFBot shows that the patch does not apply anymore as in [1]:

Rebased version attached.

Regards,
Dean
index 655f7dc..f421716

 
@@ -70,7 +71,9 @@ DELETE
       <replaceable>data_source</replaceable> row.
-      expression can use values from the <replaceable>data_source</replaceable>.
+      expression can only use values from the <replaceable>data_source</replaceable>.
 
+  <para>
 
  <refsect1>
@@ -627,7 +675,9 @@ WHEN MATCHED THEN
     This command conforms to the <acronym>SQL</acronym> standard.
   </para>
    <para>
-    The <literal>WITH</literal> clause and <literal>DO NOTHING</literal>
+    The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and
+    <literal>BY TARGET</literal> qualifiers to
+    <literal>WHEN NOT MATCHED</literal>, and <literal>DO NOTHING</literal>
     action are extensions to the <acronym>SQL</acronym> standard.
   </para>
  </refsect1>
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index b22040a..b5a18d2
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -902,8 +902,16 @@ ExecInitPartitionInfo(ModifyTableState *
 			action_state = makeNode(MergeActionState);
 			action_state->mas_action = action;
 
index 9fc5abf..8cb8933
@@ -2721,6 +2723,17 @@ ExecMerge(ModifyTableContext *context, R
 	bool		matched;
 
 	/*-----
@@ -3065,9 +3078,13 @@ lmerge_matched:
 
 						case TM_Deleted:
@@ -3272,8 +3289,14 @@ ExecInitMerge(ModifyTableState *mtstate,
index aa83dd3..38020bd
@@ -153,6 +153,8 @@ transform_MERGE_to_join(Query *parse)
 {
 	RangeTblEntry *joinrte;
 	JoinExpr   *joinexpr;
+	bool		src_only_tuples;
+	bool		tgt_only_tuples;
 	JoinType	jointype;
 	int			joinrti;
 	List	   *vars;
@@ -164,12 +166,30 @@ transform_MERGE_to_join(Query *parse)
 	vars = NIL;
 
 	/*
+	foreach_node(MergeAction, action, parse->mergeActionList)
 
@@ -215,6 +235,50 @@ transform_MERGE_to_join(Query *parse)
+		foreach_node(MergeAction, action, parse->mergeActionList)
 
 /*
index 130f7fc..f6b4c80
@@ -515,6 +516,7 @@ static Node *makeRecursiveViewSelect(cha

@@ -759,11 +761,11 @@ static Node *makeRecursiveViewSelect(cha
 
@@ -12386,50 +12388,66 @@ merge_when_list:
 
+/*
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $6;
+					$$ = (Node *) $4;
 				}
 
-					m->matched = true;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 
-					m->matched = false;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 		;
 
+merge_when_tgt_matched:
@@ -17388,6 +17406,7 @@ unreserved_keyword:
@@ -17406,6 +17425,7 @@ unreserved_keyword:
@@ -18001,6 +18021,7 @@ bare_label_keyword:
@@ -18024,6 +18045,7 @@ bare_label_keyword:
index 5f6a683..57902f5

 		/*
 
 	/*
 
 	/*

 		/*
 
 		/*
index 76c97a5..c04174c

 
index b625f47..51a1a2a
@@ -7093,6 +7093,7 @@ get_merge_query_def(Query *query, depars
 
@@ -7120,6 +7121,25 @@ get_merge_query_def(Query *query, depars
 
+	/*
@@ -7127,7 +7147,24 @@ get_merge_query_def(Query *query, depars

 		if (action->qual)
 		{
index ada711d..8349f11
@@ -4331,17 +4331,35 @@ psql_completion(const char *text, int st

 
 
index 476d55d..d9ed9ed
@@ -175,8 +175,9 @@ typedef struct Query

 
@@ -1665,7 +1666,7 @@ typedef struct CommonTableExpr
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4a15460..f7eebd8
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1732,10 +1732,18 @@ typedef struct BooleanTest
  * Transformed representation of a WHEN clause in a MERGE statement
index 2331aca..b7d5515
@@ -403,6 +403,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
@@ -426,6 +427,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW
index f87905f..b13eda0
 
 ROLLBACK;

 ROLLBACK;
 
index abc944e..f1c0094
@@ -3705,7 +3705,39 @@ BEGIN ATOMIC
index 66cb75a..7e59c9e

 
 
index 93aff4e..4ea85a2
 
 \sf merge_sf_test

 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index 7e866e3..7892693
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1570,6 +1570,7 @@ MergeAppendState
 MergeJoin
 MergeJoinClause
 MergeJoinState
+MergeMatchKind
 MergePath
 MergeScanSelCache
 MergeStmt

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 26 Jan 2024 16:57:29 +0100

> comment whether he was implying that he thought it was ready for

Well, firstly this is clearly a feature we want to have, even though
it's non-standard, because people use it and other implementations have
it.  (Eh, so maybe somebody should be talking to the SQL standard
committee about it).  As for code quality, I didn't do a comprehensive
review, but I think it is quite reasonable.  Therefore, my inclination
would be to get it committed soonish, and celebrate it widely so that
people can test it soon and complain if they see something they don't
like.

I have to say that I find the idea of booting patches as Returned with
Feedback just because of inactivity (as opposed to unresponsive authors)
rather wrong-headed, and I wish we didn't do it.

---

From: Dean Rasheed <[email-masked]>
Date: Mon, 29 Jan 2024 10:07:42 +0000

> people can test it soon and complain if they see something they don't

Thanks. I have been going over this patch again, and for the most
part, I'm pretty happy with it.

One thing that's bothering me though is what happens if a row being
merged is concurrently updated. Specifically, if a concurrent update
causes a formerly matching row to no longer match the join condition,
and there are both NOT MATCHED BY SOURCE and NOT MATCHED BY TARGET
actions, so that it's doing in full join between the source and target
relations. In this case, when the EPQ mechanism rescans the subplan
node, there will be 2 possible output tuples (one with source null,
and one with target null), and EvalPlanQual() will just return the
first one, which is a more-or-less arbitrary choice, depending on the
type of join (hash/merge), and (for a mergejoin) the values of the
inner and outer join keys. Thus, it may execute a NOT MATCHED BY
SOURCE action, or a NOT MATCHED BY TARGET action, and it's difficult
to predict which.

Arguably it's not worth worrying too much about what happens in a
corner-case concurrent update like this, when MERGE is already
inconsistent under other concurrent update scenarios, but I don't like
having unpredictable results like this, which can depend on the plan
chosen.

I think the best (and probably simplest) solution is to always opt for
a NOT MATCHED BY TARGET action in this case, so then the result is
predictable, and we can document what is expected to happen.

Regards,
Dean