JOOQ执行带部分索引的ON CONFLICT WHERE报错，直接执行SQL正常

使用JOOQ 3.17.4、pgjdbc 42.5.0、Postgres 14.3环境，执行带部分索引的ON CONFLICT ... DO UPDATE ... WHERE语句时，出现错误：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

仅通过JOOQ执行时Postgres报错，但将P6Spy打印的SQL复制到IntelliJ IDEA的SQL编辑器中执行却完全正常。

表结构定义 #

create table user_authz_request (
  id               bigint generated always as identity
    (start with 30000000)
    primary key                                not null,
  status           auth_request_status         not null,
  service_point_id bigint                      not null references service_point,
  email            varchar(256)                not null,
  client_id        varchar(256)                not null,
  id_provider      id_provider                 not null,
  subject          varchar(256)                not null,
  responding_user  bigint references app_user  null,
  description      varchar(1024)               not null,
  date_requested   timestamp without time zone default transaction_timestamp(),
  date_responded   timestamp without time zone null
);

create unique index user_authz_request_once_active_key
  on user_authz_request(service_point_id, client_id, subject)
  where status = 'REQUESTED';

JOOQ代码 #

db.insertInto(USER_AUTHZ_REQUEST).
  set(USER_AUTHZ_REQUEST.SERVICE_POINT_ID, req.getServicePointId()).
  set(USER_AUTHZ_REQUEST.STATUS, REQUESTED).
  set(USER_AUTHZ_REQUEST.EMAIL, email).
  set(USER_AUTHZ_REQUEST.CLIENT_ID, user.getClientId()).
  set(USER_AUTHZ_REQUEST.ID_PROVIDER, idProvider).
  set(USER_AUTHZ_REQUEST.SUBJECT, user.getSubject()).
  set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
  onConflict(
    USER_AUTHZ_REQUEST.SERVICE_POINT_ID,
    USER_AUTHZ_REQUEST.CLIENT_ID,
    USER_AUTHZ_REQUEST.SUBJECT).
    where(USER_AUTHZ_REQUEST.STATUS.eq(REQUESTED)).
  doUpdate().
    set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
    set(USER_AUTHZ_REQUEST.DATE_REQUESTED, LocalDateTime.now()).
  execute();

生成的SQL #

insert into api_svc.user_authz_request (service_point_id, status, email,
                                        client_id, id_provider, subject,
                                        description)
values (20000001, cast('REQUESTED' as api_svc.auth_request_status),
        'email', 'client id',
        cast('AAF' as api_svc.id_provider),
        'subject', 'first')
on conflict (service_point_id, client_id, subject)
where status = cast('REQUESTED' as api_svc.auth_request_status) do
update
set description  = 'first',
  date_requested = cast('2022-09-20T05:35:35.927+0000' as timestamp(6))

问题根源是PostgreSQL对部分索引的匹配规则：ON CONFLICT子句的筛选条件必须和部分索引的定义完全一致，包括是否存在显式类型转换。

生成的SQL中，WHERE子句里的status = cast('REQUESTED' as api_svc.auth_request_status)带了显式类型转换，但部分索引定义里是where status = 'REQUESTED'（无类型转换），这导致PostgreSQL无法识别对应的部分索引。手动执行时正常，是因为IDE环境下PostgreSQL隐式处理了类型转换，让条件与索引定义匹配；但JDBC执行时，显式转换被严格解析，触发索引匹配失败。

修改JOOQ代码，直接指定部分索引的名称，绕过条件匹配的问题：

db.insertInto(USER_AUTHZ_REQUEST).
  set(USER_AUTHZ_REQUEST.SERVICE_POINT_ID, req.getServicePointId()).
  set(USER_AUTHZ_REQUEST.STATUS, REQUESTED).
  set(USER_AUTHZ_REQUEST.EMAIL, email).
  set(USER_AUTHZ_REQUEST.CLIENT_ID, user.getClientId()).
  set(USER_AUTHZ_REQUEST.ID_PROVIDER, idProvider).
  set(USER_AUTHZ_REQUEST.SUBJECT, user.getSubject()).
  set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
  // 直接指定部分索引的名称
  onConflictOnConstraint("user_authz_request_once_active_key").
  doUpdate().
    set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
    set(USER_AUTHZ_REQUEST.DATE_REQUESTED, LocalDateTime.now()).
  execute();

这样生成的SQL会直接引用目标索引，无需额外写WHERE条件，PostgreSQL就能正确识别对应的部分索引，解决报错问题。

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