PostgreSQL+JOOQ Upsert异常：重复数据始终执行插入而非更新

问题背景 #

在PostgreSQL 14.7中维护一张info表，需基于user_id字段执行upsert操作（存在则更新、不存在则插入），且仅更新发生变化的字段。使用JOOQ 3.18.1编写Kotlin代码后，重复插入相同数据时，returning().fetch().size始终返回1，且误以为始终执行插入而非更新操作。

表结构 #

CREATE TABLE info
(
    id                          UUID PRIMARY KEY,
    user_id                     UUID        NOT NULL UNIQUE,
    email                       TEXT        NOT NULL,
    phone_number                TEXT        NOT NULL,
    first_name                  TEXT        NOT NULL,
    last_name                   TEXT        NOT NULL,
    birth_date                  TIMESTAMPTZ NOT NULL,
    gender                      TEXT        NOT NULL,
    created                     TIMESTAMPTZ NOT NULL,
    updated                     TIMESTAMPTZ NOT NULL
);

原JOOQ Kotlin代码 #

jooq.insertInto(INFO)
        .set(ID, UUID.randomUUID())
        .set(EMAIL, Info.email)
        .set(PHONE_NUMBER, Info.phoneNumber)
        .set(FIRST_NAME, Info.firstName)
        .set(LAST_NAME, Info.lastName)
        .set(BIRTH_DATE, Info.birthDate)
        .set(GENDER, Info.gender) 
        .set(USER_ID, Info.userId)
        .set(UPDATED, Instant.now())
        .set(CREATED, Instant.now())
        .onConflict(USER_ID)
        .doUpdate()
        .set(EMAIL, Info.email)
        .set(PHONE_NUMBER, Info.phoneNumber)
        .set(FIRST_NAME, Info.firstName)
        .set(LAST_NAME, Info.lastName)
        .set(BIRTH_DATE, Info.birthDate)
        .set(GENDER, Info.gender)
        .set(UPDATED, Instant.now())
        .returning().fetch().size 

生成的SQL查询 #

insert into "public"."info" (
  "id",
  "email",
  "phone_number",
  "first_name",
  "last_name",
  "birth_date",
  "gender",
  "user_id",
  "updated",
  "created"
)
values (
  '35eb89a7-24aa-4025-8ce0-ed662c74ec63', 
  'abc@abc.com', 
  '+123456789', 
  'TestName', 
  'TestLastName', 
  timestamp with time zone '1993-09-25 04:40:01+00:00', 
  'Male',  
  'e1239b01-32d9-42ec-8596-fb4e00e97b49', 
  timestamp with time zone '2023-04-13 18:30:07.507372+00:00', 
  timestamp with time zone '2023-04-13 18:30:07.509026+00:00'
)
on conflict ("user_id")
do update
set
  "email" = 'abc@abc.com',
  "phone_number" = '+123456789',
  "first_name" = 'TestName',
  "last_name" = 'TestLastName',
  "birth_date" = timestamp with time zone '1993-09-25 04:40:01+00:00',
  "gender" = 'Male',
  "updated" = timestamp with time zone '2023-04-13 18:30:07.579957+00:00'
returning 
  "public"."info"."id",
  "public"."info"."user_id",
  "public"."info"."email",
  "public"."info"."phone_number",
  "public"."info"."first_name",
  "public"."info"."last_name",
  "public"."info"."birth_date",
  "public"."info"."gender",
  "public"."info"."created",
  "public"."info"."updated"

问题排查 #

1. 返回值误解：PostgreSQL的INSERT ... ON CONFLICT ... DO UPDATE语句，无论执行插入还是更新，RETURNING都会返回1行数据，因此fetch().size始终为1，无法通过该值判断操作类型。要区分插入/更新，需对比返回的id字段：插入时返回新生成的UUID，更新时返回原有记录的id。

2. 冲突检测逻辑：user_id字段已设为UNIQUE，理论上重复插入相同user_id会触发冲突并执行更新。若未触发，需手动验证数据库中是否已存在对应user_id的记录：

   SELECT * FROM info WHERE user_id = 'e1239b01-32d9-42ec-8596-fb4e00e97b49';
   

3. 代码变量校验：确认两次执行时Info.userId变量是否为同一个UUID值，避免代码中意外修改user_id导致冲突未触发。

修复方案（实现仅更新变化字段） #

原代码会强制更新所有字段（即使值未变化），需添加WHERE条件仅在字段值不同时执行更新，同时利用EXCLUDED引用插入语句中的值：

jooq.insertInto(INFO)
    .set(ID, UUID.randomUUID())
    .set(EMAIL, Info.email)
    .set(PHONE_NUMBER, Info.phoneNumber)
    .set(FIRST_NAME, Info.firstName)
    .set(LAST_NAME, Info.lastName)
    .set(BIRTH_DATE, Info.birthDate)
    .set(GENDER, Info.gender)
    .set(USER_ID, Info.userId)
    .set(UPDATED, Instant.now())
    .set(CREATED, Instant.now())
    .onConflict(USER_ID)
    .doUpdate()
    .set(EMAIL, Info.email)
    .set(PHONE_NUMBER, Info.phoneNumber)
    .set(FIRST_NAME, Info.firstName)
    .set(LAST_NAME, Info.lastName)
    .set(BIRTH_DATE, Info.birthDate)
    .set(GENDER, Info.gender)
    .set(UPDATED, Instant.now())
    // 仅当字段值与现有记录不同时才更新
    .where(
        EXCLUDED.EMAIL.ne(INFO.EMAIL)
            .or(EXCLUDED.PHONE_NUMBER.ne(INFO.PHONE_NUMBER))
            .or(EXCLUDED.FIRST_NAME.ne(INFO.FIRST_NAME))
            .or(EXCLUDED.LAST_NAME.ne(INFO.LAST_NAME))
            .or(EXCLUDED.BIRTH_DATE.ne(INFO.BIRTH_DATE))
            .or(EXCLUDED.GENDER.ne(INFO.GENDER))
    )
    .returning()
    .fetch()

此时，若所有字段值均无变化，WHERE条件不满足，DO UPDATE不会执行，returning().fetch().size将返回0，可通过该值判断是否有更新发生。

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