This allows MERGE to UPDATE or DELETE target rows where there is no
matching source row. In addition, it allows the existing "WHEN NOT
MATCHED" syntax to include an optional "BY TARGET" to make its meaning
more explicit. E.g.,
MERGE INTO tgt USING src ON ...
WHEN NOT MATCHED BY SOURCE THEN UPDATE/DELETE ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...
AFAIK, this is not part of the standard (though I only have a very old
draft copy). It is supported by at least 2 other major DB vendors
though, and I think it usefully rounds off the set of possible MERGE
actions.
Attached is a WIP patch. I haven't updated the docs yet, and there are
probably a few other things to tidy up and test, but the basic
functionality is there.
Regards,
Dean
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 4cec12a..ed538e5
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -142,7 +142,6 @@ transform_MERGE_to_join(Query *parse)
{
RangeTblEntry *joinrte;
JoinExpr *joinexpr;
- JoinType jointype;
int joinrti;
List *vars;
@@ -152,20 +151,10 @@ transform_MERGE_to_join(Query *parse)
/* XXX probably bogus */
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.
- */
- if (parse->mergeUseOuterJoin)
- jointype = JOIN_RIGHT;
- else
- jointype = JOIN_INNER;
-
/* Manufacture a join RTE to use. */
joinrte = makeNode(RangeTblEntry);
joinrte->rtekind = RTE_JOIN;
- joinrte->jointype = jointype;
+ joinrte->jointype = parse->mergeJoinType;
joinrte->joinmergedcols = 0;
joinrte->joinaliasvars = vars;
joinrte->joinleftcols = NIL; /* MERGE does not allow JOIN USING */
@@ -189,7 +178,7 @@ transform_MERGE_to_join(Query *parse)
* Create a JOIN between the target and the source relation.
*/
joinexpr = makeNode(JoinExpr);
- joinexpr->jointype = jointype;
+ joinexpr->jointype = parse->mergeJoinType;
joinexpr->isNatural = false;
joinexpr->larg = (Node *) makeNode(RangeTblRef);
((RangeTblRef *) joinexpr->larg)->rtindex = parse->resultRelation;
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
new file mode 100644
index 137b283..1a4e13e
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -157,15 +157,14 @@ preprocess_targetlist(PlannerInfo *root)
/*
* Add resjunk entries for any Vars used in each action's
* targetlist and WHEN condition that belong to relations other
- * than target. Note that aggregates, window functions and
- * placeholder vars are not possible anywhere in MERGE's WHEN
- * clauses. (PHVs may be added later, but they don't concern us
- * here.)
+ * than target. Note that aggregates and window functions are not
+ * possible anywhere in MERGE's WHEN clauses, but PlaceHolderVars
+ * may have been added by subquery pullup.
*/
vars = pull_var_clause((Node *)
list_concat_copy((List *) action->qual,
action->targetList),
- 0);
+ PVC_INCLUDE_PLACEHOLDERS);
foreach(l2, vars)
{
Var *var = (Var *) lfirst(l2);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 63b4baa..95531ab
--- 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; }
@@ -16961,6 +16973,7 @@ unreserved_keyword:
| SIMPLE
| SKIP
| SNAPSHOT
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -16979,6 +16992,7 @@ unreserved_keyword:
| SYSTEM_P
| TABLES
| TABLESPACE
+ | TARGET
| TEMP
| TEMPLATE
| TEMPORARY
@@ -17555,6 +17569,7 @@ bare_label_keyword:
| SMALLINT
| SNAPSHOT
| SOME
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -17578,6 +17593,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 3844f2b..f565072
--- 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,9 @@ transformMergeStmt(ParseState *pstate, M
Query *qry = makeNode(Query);
ListCell *l;
AclMode targetPerms = ACL_NO_RIGHTS;
- bool is_terminal[2];
+ bool is_terminal[3];
+ bool left_join;
+ bool right_join;
Index sourceRTI;
List *mergeActionList;
Node *joinExpr;
@@ -127,10 +143,12 @@ transformMergeStmt(ParseState *pstate, M
*/
is_terminal[0] = false;
is_terminal[1] = false;
+ is_terminal[2] = false;
+ left_join = false;
+ right_join = 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
@@ -156,13 +174,32 @@ transformMergeStmt(ParseState *pstate, M
* Check for unreachable WHEN clauses
*/
if (mergeWhenClause->condition == NULL)
- is_terminal[when_type] = true;
- else if (is_terminal[when_type])
+ is_terminal[mergeWhenClause->matchKind] = true;
+ else if (is_terminal[mergeWhenClause->matchKind])
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("unreachable WHEN clause specified after unconditional WHEN clause")));
+
+ /* Is left/right/full outer join required? */
+ if (mergeWhenClause->commandType != CMD_NOTHING)
+ {
+ if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+ left_join = true;
+ if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET)
+ right_join = true;
+ }
}
+ /* Join type required */
+ if (left_join && right_join)
+ qry->mergeJoinType = JOIN_FULL;
+ else if (left_join)
+ qry->mergeJoinType = JOIN_LEFT;
+ else if (right_join)
+ qry->mergeJoinType = JOIN_RIGHT;
+ else
+ qry->mergeJoinType = JOIN_INNER;
+
/*
* Set up the MERGE target table. The target table is added to the
* namespace below and to joinlist in transform_MERGE_to_join, so don't
@@ -251,15 +288,12 @@ transformMergeStmt(ParseState *pstate, M
foreach(l, stmt->mergeWhenClauses)
{
MergeWhenClause *mergeWhenClause = lfirst_node(MergeWhenClause, l);
+ MergeMatchKind matchKind = mergeWhenClause->matchKind;
MergeAction *action;
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->matched = matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET;
/*
* Set namespace for the specific action. This must be done before
@@ -280,6 +314,47 @@ transformMergeStmt(ParseState *pstate, M
EXPR_KIND_MERGE_WHEN, "WHEN");
/*
+ * If we are doing a left or full join that includes target-only rows
+ * (NOT MATCHED BY SOURCE), add additional WHEN conditions to each
+ * MATCHED and NOT MATCHED BY SOURCE action to distinguish them from
+ * one another.
+ *
+ * MATCHED rows are identified by a "source IS DISTINCT FROM NULL"
+ * clause, and NOT MATCHED BY SOURCE rows by a "source IS NOT DISTINCT
+ * FROM NULL" clause.
+ *
+ * Nothing is needed for NOT MATCHED BY TARGET rows, since they are
+ * identified in the executor by checking the target identity column.
+ */
+ if (left_join && action->matched)
+ {
+ NullTest *ntest;
+
+ /* Make a "source IS [NOT] DISTINCT FROM NULL" clause */
+ ntest = makeNode(NullTest);
+
+ ntest->arg = (Expr *) makeVar(sourceRTI,
+ InvalidAttrNumber,
+ RECORDOID,
+ -1,
+ InvalidOid,
+ 0);
+ ntest->nulltesttype =
+ matchKind == MERGE_WHEN_MATCHED ? IS_NOT_NULL : IS_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/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 34bc640..887a5d1
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -159,7 +159,7 @@ typedef struct Query
* also USING clause for MERGE */
List *mergeActionList; /* list of actions for MERGE (only) */
- bool mergeUseOuterJoin; /* whether to use outer join */
+ JoinType mergeJoinType; /* target to source join type */
List *targetList; /* target list (of TargetEntry) */
@@ -1586,6 +1586,14 @@ typedef struct CommonTableExpr
((Query *) (cte)->ctequery)->targetList : \
((Query *) (cte)->ctequery)->returningList)
+/* MATCHED/NOT MATCHED specification for MergeWhenClause */
+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
@@ -1595,7 +1603,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) */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
new file mode 100644
index 957ee18..fd94b0b
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -391,6 +391,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)
@@ -414,6 +415,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 6c8a18f..e99623a
--- 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 not matched
+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
@@ -1436,6 +1522,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 98fe104..c641214
--- 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 not matched
+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
@@ -916,6 +970,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
