Is there a way to know what operation was performed in MERGE?

Issue

Using jOOQ and PostgreSQL, is there a way to know what operation was executed for a MERGE, INSERT or UPDATE?

E.g. using the example from jOOQ's documentation:

create.mergeInto(AUTHOR)
      .using(create.selectOne())
      // Suppose the last name is unique (natural key) in my case
      .on(AUTHOR.LAST_NAME.eq("Hitchcock"))
      .whenMatchedThenUpdate()
      .set(AUTHOR.FIRST_NAME, "John")
      .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

Ideally, I'd get both the resulting row and what operation was executed, if possible.

Note: Not a duplicate of this question. That one is SQL Server specific. Mine is PostgreSQL and jOOQ specific.

Solution

PostgreSQL MERGE can't use a RETURNING clause for its underlying INSERT and UPDATE statements, so you're just left with a total number of rows that were affected, without any indication of how they were affected. Doc:

On successful completion, a MERGE command returns a command tag of the form

MERGE total_count

The total_count is the total number of rows changed (whether inserted, updated, or deleted).

There is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and DELETE cannot contain RETURNING or WITH clauses.

You could use the older upsert syntax INSERT...ON CONFLICT DO UPDATE which is able to use a RETURNING clause, and in it, check xmax system column of affected rows: inserted ones will always have zero, the updated ones will have non-zero. Example.

jOOQ offers both .onConflict() and .returningResult() (just .returning() before 3.11). The problematic part seems to be the exposure of the hidden system column, that you might not be able to reference straight away. If that's still the case, you'll need a plain SQL field (found here).

In case it's too much trouble to reach xmax and you don't mind littering your schema, it's possible to set up a reflector column instead of trying to use the hidden xmax. Or, if you're sure your insert batch never contains the conflict resolution values, count the ones that ended up using them.

Answered By - Zegarek