基于jOOQ与PostgreSQL，如何获知MERGE执行的操作类型及结果行？

要实现这个需求，核心是利用PostgreSQL的系统字段和jOOQ的RETURNING子句，直接从MERGE语句的返回结果里拿到操作类型和行数据，具体步骤如下：

实现思路 #

PostgreSQL的系统字段xmax可以用来区分操作类型：

• 执行INSERT时，新插入行的xmax值为0
• 执行UPDATE时，被更新行的xmax会被设置为执行更新的事务ID（非0）

结合jOOQ的MERGE DSL和RETURNING子句，我们可以自定义返回一个标记操作类型的字段，同时返回目标行的所有数据。

代码示例 #

把你的示例代码修改为：

// 执行MERGE并返回结果行和操作类型
Result<Record> result = create.mergeInto(AUTHOR)
    .using(create.selectOne())
    .on(AUTHOR.LAST_NAME.eq("Hitchcock"))
    .whenMatchedThenUpdate()
    .set(AUTHOR.FIRST_NAME, "John")
    .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
    .values("Hitchcock")
    .returning(
        // 返回表中需要的字段
        AUTHOR.ID,
        AUTHOR.LAST_NAME,
        AUTHOR.FIRST_NAME,
        // 自定义操作类型字段
        DSL.field("CASE WHEN xmax = 0 THEN 'INSERT' ELSE 'UPDATE' END", SQLDataType.VARCHAR).as("operation")
    )
    .fetch();

// 遍历结果处理数据
for (Record record : result) {
    Long authorId = record.get(AUTHOR.ID);
    String lastName = record.get(AUTHOR.LAST_NAME);
    String firstName = record.get(AUTHOR.FIRST_NAME);
    String operationType = record.get("operation", String.class);
    
    // 根据操作类型做后续逻辑
    if ("INSERT".equals(operationType)) {
        // 处理插入逻辑
    } else {
        // 处理更新逻辑
    }
}

关键说明 #

1. 版本要求：确保你的PostgreSQL版本是15及以上，因为PostgreSQL在15版本才正式支持MERGE语法
2. 替代方案（如果用UPSERT）：如果你的PostgreSQL版本低于15，建议用INSERT ... ON CONFLICT实现UPSERT，同样可以通过RETURNING和xmax区分操作类型
3. 可靠性：xmax是PostgreSQL内置的系统字段，用来跟踪行的更新/删除事务，用它判断操作类型是稳定可靠的

内容的提问来源于stack exchange，提问作者kaqqao