PostgreSQL 16迁移时jOOQ生成Merge Upsert语句失效问题求助

我正在将应用从PostgreSQL 12 + jOOQ 3.15.4（专业版）迁移至PostgreSQL 16 + jOOQ 3.18.7（专业版）。部分使用onDuplicateKeyUpdate()的查询出现异常：迁移前jOOQ会生成可正常执行的insert into .. on conflict .. do update ..语句，但现在生成的merge into语句在PostgreSQL中无法运行，想了解其中原因，PostgreSQL是否支持merge into语法？

正常生成INSERT ... ON CONFLICT的示例 #

模拟代码：

return DSL
    .insertInto(
        fruitTable,
        fruitTable.FRUIT_ID,
        fruitTable.NAME,
    )
    .values(
        mandate.mandateId,
        "Banana",
    )
    .onDuplicateKeyUpdate()
    .set(fruitTable.NAME, "Apple")

执行生成的SQL：

insert into "public"."fruit" as "fr" ("fruit_id", "name") values ('2f8a4b65-7e1e-49d4-8530-ae2b6bb08857', 'Banana') on conflict ("fruit_id") do update set "name" = 'Apple';

-- is OK!

业务代码生成MERGE语句的异常场景 #

场景1：别名引用错误 #

业务代码：

return DSL
        .insertInto(
            mandateTable,
            mandateTable.MANDATE_ID,
            mandateTable.LAST_UPDATE,
            )
        .values(
            mandate.mandateId,
            LocalDateTime.now(zid),
        )
    .onDuplicateKeyUpdate()
    .set(mandateTable.LAST_UPDATE, LocalDateTime.now(zid))

执行SQL及错误：

-- Executing SQL:

merge into "public"."mandate" as "m" using (select '2f8a4b65-7e1e-49d4-8530-ae2b6bb08857', timestamp '2023-12-07 18:05:33.033756553') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 18:05:33.033877463' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update");

错误信息：

org.jooq.exception.DataAccessException:
SQL [merge into "public"."mandate" as "m" using (select '2f8a4b65-7e1e-49d4-8530-ae2b6bb08857', timestamp '2023-12-07 18:05:33.033756553') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 18:05:33.033877463' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update")]; Batch entry 1 merge into "public"."mandate" as "m" using (select '2f8a4b65-7e1e-49d4-8530-ae2b6bb08857', timestamp '2023-12-07 18:05:33.033756553') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 18:05:33.033877463' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update")
was aborted: ERROR: invalid reference to FROM-clause entry for table "mandate"
 Hint: Perhaps you meant to reference the table alias "m".

场景2：移除别名后出现类型匹配错误 #

调整后的代码：

return DSL
        .insertInto(
            Tables.MANDATE,
            Tables.MANDATE.MANDATE_ID,
            Tables.MANDATE.LAST_UPDATE,
            )
        .values(
            Tables.MANDATE.mandateId,
            LocalDateTime.now(zid),
        )
    .onDuplicateKeyUpdate()
    .set(Tables.MANDATE.LAST_UPDATE, LocalDateTime.now(zid))

执行SQL及错误：

-- Executing SQL:

merge into "public"."mandate" using (select '39d98a7c-39f2-4898-840d-90df7b22884b', timestamp '2023-12-07 17:57:27.106425382') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 17:57:27.106553633' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update");

错误信息：

org.jooq.exception.DataAccessException:
SQL [merge into "public"."mandate" using (select '39d98a7c-39f2-4898-840d-90df7b22884b', timestamp '2023-12-07 17:57:27.106425382') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 17:57:27.106553633' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update")]; Batch entry 1 merge into "public"."mandate" using (select '39d98a7c-39f2-4898-840d-90df7b22884b', timestamp '2023-12-07 17:57:27.106425382') as "t" ("mandate_id", "last_update") on ("public"."mandate"."mandate_id" = "t"."mandate_id" or "public"."mandate"."portfolio_id" = null) when matched then update set "last_update" = timestamp '2023-12-07 17:57:27.106553633' when not matched then insert ("mandate_id", "last_update") values ("t"."mandate_id", "t"."last_update")
was aborted: ERROR: operator does not exist: uuid = text
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

推测的解决方向 #

• 强制jOOQ生成insert into .. on conflict语句替代merge into
• 确保数据库将ID字段正确识别为uuid类型（当前生成的SQL中ID被当作普通字符串处理）

内容的提问来源于stack exchange，提问作者Torbjörn Österdahl