Can MERGE activate multiple WHEN MATCHED AND conditions

Question

Can multiple WHEN MATCHED AND ... conditions be activated within a single SQL MERGE?

MERGE INTO translation t
USING ( SELECT d.iddiagram
             , rt.uuid_translation
             , rt.context
             , r.idrectangle
             , rt.x
             , rt.y
             , rt.z
        FROM json_to_recordset('${JSON.stringify(data.translation)}') 
          AS rt(${column_list.translation})
        JOIN json_to_recordset('${JSON.stringify(data.rectangle)}') 
          AS rr(${column_list.rectangle}) ON rt.idrectangle=rr.idrectangle
        JOIN rectangle r ON r.uuid_rectangle=rr.uuid_rectangle
        JOIN diagram d ON d.uuid_diagram='${uuid_diagram}'
) AS rt
ON t.uuid_translation = rt.uuid_translation
WHEN NOT MATCHED BY TARGET THEN
    INSERT (iddiagram, uuid_translation, context, idrectangle, x, y, z)
    VALUES (rt.*)
WHEN MATCHED AND t.context != rt.context 
   THEN UPDATE SET context  = rt.context
WHEN MATCHED AND t.x != rt.x 
   THEN UPDATE SET x  = rt.x
WHEN MATCHED AND t.y != rt.y 
   THEN UPDATE SET y  = rt.y
WHEN MATCHED AND t.z != rt.z 
   THEN UPDATE SET z  = rt.z
WHEN NOT MATCHED BY SOURCE
   AND EXISTS(SELECT FROM diagram d 
               WHERE d.iddiagram=t.iddiagram 
                 AND d.uuid_diagram='${uuid_diagram}')
   THEN DELETE

In the above example what would happen when simultaneously t.x != rt.x and t.y != rt.y and t.z != rt.z? How many update(s) would get executed?

Answer 1

1. Can multiple WHEN MATCHED AND ... conditions be activated within a single SQL Merge?

Not for a single incoming row at once, not multiple conditions specified by separate when matched cases. Only multiple conditions combined into one when matched case can "activate" for a given row. Different incoming rows can match different cases.
Widening the doc quote already mentioned by @Adrian Klaver:

For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.

Which is easy to test: _{demo at db<>fiddle}

create table test(id,c1,c2)as values(1,'old value','old value');

merge into test
using(values(1))as rt(id)
on test.id=rt.id
when matched and true then update set c1='1st case applied'
when matched and true then update set c2='2nd case also applied'
returning merge_action(),test.*;

merge_action	id	c1	c2
UPDATE	1	1st case applied	old value

---

2. In the above example what would happen when simultaneously t.x != rt.x and t.y != rt.y and t.z != rt.z? How many update(s) would get executed ?

As already pointed out, unless you make them one single case, only the first match applies, meaning the incoming row matched on uuid_translation will only cause an update of the x column. All your matched conditions seem to boil down to a single

when matched then update 
set context=rt.context
   ,x=rt.x
   ,y=rt.y
   ,z=rt.z

If you use that, all four columns will get updated at once.

---

3. is there a compact PostgreSQL syntax to write WHEN MATCHED AND ((t.x != rt.x) OR (t.y != rt.y) OR (t.z != rt.z)) THEN UPDATE SET x=rt.x, y=rt.y, z=rt.z? Luckily there cannot be nulls.

You can use row/tuple syntax:

when matched and (t.x,t.y,t.z)!=(rt.x,rt.y,rt.z)

which in this case is entirely equivalent - Postgres rewrites one as the other:

explain verbose select (c1,c2)!=('a','b') from test;

Output: ((c1 <> 'a'::text) OR (c2 <> 'b'::text))

explain verbose select c1!='a' or c2!='b' from test;

Output: ((c1 <> 'a'::text) OR (c2 <> 'b'::text))

If these were nullable, you could switch to is distinct from comparison predicate instead of plain, three-state inequality:

when matched and (t.x,t.y,t.z) is distinct from (rt.x,rt.y,rt.z)