jooq-识别postgres UNIQUE约束的问题

问题描述

我正在尝试:

context.insertInto(table(ERROR_TABLE))
  .set(valuesMap)
  .onConflictOnConstraint(constraint(name("push_def_rec_error_idx"))
  .doUpdate()
  .set(field(name(fieldname)), value)
  .execute();

我告诉我一个错误:

表定义(通过\d+ table_name):

...

Indexes:
    "push_record_error_pkey" PRIMARY KEY, btree (push_record_error_id)
    "push_def_rec_error_idx" UNIQUE, btree (push_definition_id, rec_id)

我在做什么错了?

这用于 SQLDialect.POSTGRES_10

推荐答案

您为索引命名的方式，我假设您对这些列没有约束，但有一个UNIQUE INDEX:

The way you named your indexes, I'm assuming you don't have a constraint on those columns, but a UNIQUE INDEX:

CREATE TABLE T (a INT PRIMARY KEY, b INT, c INT);
CREATE UNIQUE INDEX u ON t(b);

INSERT INTO T (a, b, c)
VALUES (1, 2, 3)
ON CONFLICT ON CONSTRAINT u
DO UPDATE SET c = 4
RETURNING *;

以上内容产生:

[42704]: ERROR: constraint "u" for table "t" does not exist

但是，将索引变成约束:

However, turn the index into a constraint:

DROP INDEX u;
ALTER TABLE t ADD CONSTRAINT u UNIQUE (b);

INSERT语句现在可以正常工作.

And the INSERT statement now works.

在此处查看有关唯一约束和唯一索引之间区别的说明.这和jOOQ没什么关系

See an explanation here about the difference between unique constraints and unique indexes. This is not really related to jOOQ

这篇关于jooq-识别postgres UNIQUE约束的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！