Returning count of updated rows when UPserting to a Postgres table using jOOQ

Question

I am upserting some data to a Postgres table using jOOQ's insertInto() and onDuplicateKeyUpdate() methods. I want to know later how many duplicates were in my data and hence need to return if a row was inserted or updated.

From my postgres specific research so far, I found RETURNING (not MY_TABLE.xmax = 0) AS updated to be a valid option. However, the auto-generated Java table classes from jOOQ don't seem to give me access to the system columns of postgres like xmax.

Here is my query so far:

dsl.insertInto(MY_TABLE)
        .columns(
                // pkey columns
                MY_TABLE.SHIFT,
                MY_TABLE.DATE_UTC,
                MY_TABLE.TIME_UTC,

                MY_TABLE.DURATION,
        )
        .values(
                shiftId,
                utcDateId,
                utcTime,
                duration
        )
        .onDuplicateKeyUpdate()
        .set(MY_TABLE.DURATION, newDuration)
        .returning((MY_TABLE.xmax = 0).`às`("inserted"))
        .execute()

This causes the following compile time error:

Error: Kotlin: Unresolved reference: XMAX

I have rechecked my Maven jOOQ table generation configuration and I am not excluding any columns. I have also read through everything I could find on jOOQ's own website but found no useful information for this specific use-case.

Any tips on what I could do here?

Answer 1

In this case you should use jOOQ's SQL templating. Specifically look at the DSL.field() method. Something like this: field("my_table.xmax", int.class).eq(0).