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

From: Dean Rasheed <[email-masked]>
Date: Sun, 19 Mar 2023 09:10:15 +0000

I see the PlaceHolderVar issue turned out to be a pre-existing bug after all.
Rebased version attached.

Regards,
Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index b87ad5c..1482ede
--- 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 fd6ca8a..861d35e
--- 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 3fa2b93..a1307f8
--- 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.
@@ -2706,6 +2708,20 @@ 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.  In the case of concurrent updates
+	 * and rechecks discussed below, the target tuple might be modified, but
+	 * not the source tuple, and so there is no danger of a WHEN MATCHED case
+	 * becoming a WHEN NOT MATCHED BY SOURCE case, or vice versa.  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
@@ -3242,8 +3258,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 870d84b..3b32c8d
--- 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 clauses, 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;
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index efe88cc..e1ebc8d
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -273,6 +273,7 @@ static Node *makeRecursiveViewSelect(cha
 	struct SelectLimit *selectlimit;
 	SetQuantifier setquantifier;
 	struct GroupClause *groupclause;
+	MergeMatchKind mergematch;
 	MergeWhenClause *mergewhen;
 	struct KeyActions *keyactions;
 	struct KeyAction *keyaction;
@@ -512,6 +513,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
 
@@ -740,11 +742,11 @@ static Node *makeRecursiveViewSelect(cha
 
 	SAVEPOINT 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
@@ -12260,49 +12262,59 @@ merge_when_list:
 		;
 
 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; }
@@ -16968,6 +16980,7 @@ unreserved_keyword:
 			| SIMPLE
 			| SKIP
 			| SNAPSHOT
+			| SOURCE
 			| SQL_P
 			| STABLE
 			| STANDALONE_P
@@ -16986,6 +16999,7 @@ unreserved_keyword:
 			| SYSTEM_P
 			| TABLES
 			| TABLESPACE
+			| TARGET
 			| TEMP
 			| TEMPLATE
 			| TEMPORARY
@@ -17563,6 +17577,7 @@ bare_label_keyword:
 			| SMALLINT
 			| SNAPSHOT
 			| SOME
+			| SOURCE
 			| SQL_P
 			| STABLE
 			| STANDALONE_P
@@ -17586,6 +17601,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 d886637..fca2bd9
--- 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 MATCH 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	/* mergeWhenClause->matchKind == 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,7 +112,8 @@ transformMergeStmt(ParseState *pstate, M
 	Query	   *qry = makeNode(Query);
 	ListCell   *l;
 	AclMode		targetPerms = ACL_NO_RIGHTS;
-	bool		is_terminal[2];
+	bool		is_terminal[3];
+	bool		tgt_only_tuples;
 	Index		sourceRTI;
 	List	   *mergeActionList;
 	Node	   *joinExpr;
@@ -125,12 +140,13 @@ 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;
+	tgt_only_tuples = 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 +171,22 @@ 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;
+
+		/*
+		 * Test if we have a WHEN NOT MATCHED BY SOURCE action that processes
+		 * unmatched tuples from the target relation.  If so, additional WHEN
+		 * conditions are added below to differentiate the various match
+		 * kinds.
+		 */
+		if (mergeWhenClause->commandType != CMD_NOTHING &&
+			mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+			tgt_only_tuples = true;
 	}
 
 	/*
@@ -255,11 +281,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
@@ -280,6 +302,51 @@ transformMergeStmt(ParseState *pstate, M
 											EXPR_KIND_MERGE_WHEN, "WHEN");
 
 		/*
+		 * 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 that tuples
+		 * from the source match or do not match, as necessary.
+		 *
+		 * This distinguishes NOT MATCHED BY SOURCE actions (identified by a
+		 * "source IS NOT DISTINCT FROM NULL" clause) from MATCHED actions
+		 * (identified by a "source IS DISTINCT FROM NULL" clause).
+		 *
+		 * Additionally, a "source IS DISTINCT FROM NULL" clause is required
+		 * for 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)
+		{
+			bool		src_null;
+			NullTest   *ntest;
+
+			/* Make a "source IS [NOT] DISTINCT FROM NULL" clause */
+			src_null = action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE;
+
+			ntest = makeNode(NullTest);
+
+			ntest->arg = (Expr *) makeVar(sourceRTI,
+										  InvalidAttrNumber,
+										  RECORDOID,
+										  -1,
+										  InvalidOid,
+										  0);
+			ntest->nulltesttype = src_null ? IS_NULL : IS_NOT_NULL;
+			ntest->argisrow = false;
+			ntest->location = -1;
+
+			/* Combine it with the action's WHEN condition */
+			if (action->qual == NULL)
+				action->qual = (Node *) ntest;
+			else
+				action->qual =
+					(Node *) makeBoolExpr(AND_EXPR,
+										  list_make2(ntest, action->qual),
+										  -1);
+		}
+
+		/*
 		 * Transform target lists for each INSERT and UPDATE action stmt
 		 */
 		switch (action->commandType)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 42e87b9..941c64b
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4237,17 +4237,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 028588f..e4ce377
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -183,8 +183,6 @@ 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);
 
 	List	   *targetList;		/* target list (of TargetEntry) */
 
@@ -1662,6 +1660,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 +1677,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 +1693,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 753e9ee..b213b99
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -392,6 +392,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)
@@ -415,6 +416,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 1ddc68b..8ad2a62
--- 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/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 29a3548..76bb9fd
--- 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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 21 Mar 2023 11:28:49 +0100

> +			| WHEN NOT MATCHED BY SOURCE	{ $$ = MERGE_WHEN_NOT_MATCHED_BY_SOURCE; }

I think a one-line comment on why this "matched" production matches "NOT
MATCHED BY" would be useful.  I think you have a big one in
transformMergeStmt already.

> +										  list_make2(ntest, action->qual),

Hmm, I think ->qual is already in implicit-and form, so do you really
need to makeBoolExpr, or would it be sufficient to append this new
condition to the list?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)