Support MERGE

[max-muoto]

While pgbulk.upsert works well for cases in tables that have uniquness constraints, in cases where you'd like to upsert on models without uniqueness constraints, a combination of multiple uniqueness constraints, or with deferred uniquness constraints. MERGE solves all of these use-cases, and as of Postgres 17 supports the ability to return results.

I personally like how jooq for Java supports MERGE, with the ability to dynamically add match/non-match statements:

MERGE INTO AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME) VALUES ('Hitchcock');

becomes:

create.mergeInto(AUTHOR)
      .using(create.selectOne())
      .on(AUTHOR.LAST_NAME.eq("Hitchcock"))
      .whenMatchedThenUpdate()
      .set(AUTHOR.FIRST_NAME, "John")
      .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

[wesleykendall]

Going to move this over to a discussion since that's where I've been keeping feature requests. I do think that a merge function makes sense in pgbulk!