digoal
2024-04-01
PostgreSQL , PolarDB , DuckDB , MERGE ... WHEN NOT MATCHED BY SOURCE
Merge语法支持当目标表存在, 源端不存在的row的action.
Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
author Dean Rasheed <dean.a.rasheed@gmail.com>
Sat, 30 Mar 2024 10:00:26 +0000 (10:00 +0000)
committer Dean Rasheed <dean.a.rasheed@gmail.com>
Sat, 30 Mar 2024 10:00:26 +0000 (10:00 +0000)
commit 0294df2f1f842dfb0eed79007b21016f486a3c6c
tree d01da8c03b91a25a438f05dd7dccdb95d13f8f92 tree
parent 46e5441fa536b89c1123f270fdfeeb72c320b901 commit | diff
Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE
actions, which operate on rows that exist in the target relation, but
not in the data source. These actions can execute UPDATE, DELETE, or
DO NOTHING sub-commands.
This is in contrast to already-supported WHEN NOT MATCHED actions,
which operate on rows that exist in the data source, but not in the
target relation. To make this distinction clearer, such actions may
now be written as WHEN NOT MATCHED BY TARGET.
Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is
equivalent to writing WHEN NOT MATCHED BY TARGET.
Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing.
Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com
+ UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN
+ UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+ RETURNING merge_action(), t.*;
+
+merge_action|key|val
+------------+---+------------------------------------------------------
+UPDATE | 3|setup1 updated by merge1 source not matched by merge2a
+INSERT | 1|merge2a
+(2 rows)
