CODEWITHSUNDEEP
postgresqljooq
acme-j
acme-j

Reputation: 99

jooq - problem recognizing postgres UNIQUE constraint

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

Upvotes: 1

Views: 1520

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220987

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

Upvotes: 1

Related Questions