使用JooQ 3.17.4、pgjdbc 42.5.0和postgres 14.3
我知道这里有这样的答案:https://stackoverflow.com/a/67292162/924597-我尝试过使用非限定字段名,但没有任何区别。
我尝试使用部分索引发出执行"on conflict .. do update ... where"的SQL语句,但是我收到错误:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
奇怪的是,Postgres给出了失败,* 只有当通过JOOQ * 发出SQL时,如果我从控制台复制SQL(如P6Spy所打印的)并粘贴到IntelliJ IDEA的SQL编辑中-* 完全相同的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';
JOQ代码:
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))
以上是在我的服务器中运行时失败的SQL,但当我通过IntelliJ执行它时,它工作正常。
我哪里做错了?
1条答案
按热度按时间xxhby3vn1#
从jOOQ 3.18和#12531开始,jOOQ会自动内联
WHERE
子句中的所有绑定变量,因为使用绑定值几乎没有意义。这是jOOQ常规行为中的一个大例外,因为只有极少数情况下:在绑定值自动内联的大多数其他情况下,它们在语法上也不正确,因此自动内联可能争议较少。
在Python 3.18和#12531发布之前,您只需手动内联绑定值即可:
另见:
实际上,这和你刚才提到的问题是一样的:Upsert with "on conflict do update" with partial index in jOOQ,只是一种不同的表现形式