MERGE ... WHEN NOT MATCHED BY SOURCE

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: MERGE ... WHEN NOT MATCHED BY SOURCE
Date: 2022-12-30 16:56:17
Message-ID: CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
support-merge-when-not-matched-by-source-v1.patch text/x-patch 24.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joseph Koshakow 2022-12-30 17:17:36 Re: Infinite Interval
Previous Message Tom Lane 2022-12-30 16:50:36 Re: split TOAST support out of postgres.h