jooq - problem recognizing postgres UNIQUE constraint

Question

Am attempting :

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

Am getting an error telling me:

ERROR: constraint "push_def_rec_error_idx" for table "push_error" does not exist

Table definition (via \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)

What am I doing wrong?

This is for SQLDialect.POSTGRES_10

In my (albeit ignorant) mind they are one and the same. Perhaps not to jooq? — acme-j, Jun 10 '20 at 14:15
Also, not to PostgreSQL. `CREATE UNIQUE INDEX` and `ALTER TABLE .. ADD CONSTRAINT` are not the same thing. Does the statement produced by jOOQ work if you run it in PostgreSQL directly? — Lukas Eder, Jun 10 '20 at 16:30

Lukas Eder · Answer 1 · 2020-06-11T10:49:53.380

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 *;

The above produces:

[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);

And the INSERT statement now works.

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

jooq - problem recognizing postgres UNIQUE constraint

1 Answers1

Linked