CODEWITHSUNDEEP
javapostgresqljooq
Victoria K
Victoria K

Reputation: 108

Batch insert onDuplcateKeyIgnore JOOQ

I have a class that looks like

public class ABLink {
 private Long aId;
 private Long bId;
}

this class has a corresponding entity in the database(Postgresql) that looks like:

CREATE TABLE a_b_link(
  a_id BIGINT NOT NULL,
  b_id BIGINT NOT NULL,
  CONSTRAINT a_b_link_pk
  PRIMARY KEY (a_id, b_id)
 );

And I am trying to perform batch insert using jooq loader api in a such way:

 dsl.loadInto(A_B_LINK).batchAll()
                .onDuplicateKeyUpdate()
                .loadRecords(records)
                .fields(A_B_LINK.fields())
                .execute();

Cause I am trying to make a batch for the such logic of insert:

 insertInto(A_B_LINK).set(record).onDuplicateKeyUpdate().set(record).execute()

But I've faced such errors like:

 Batch entry 0 insert into "schema"."a_b_link" ("a_id", "b_id") values (3273, 8) on conflict ("a_id", "b_id") do update set [ no fields are updated ] was aborted: ERROR: syntax error at or near "["

Cause there are no fields for update. Only this ids. I've tried to use onDuplicateKeyUpdate in loader api for batch, but I receive an error:

Cannot apply batch loading with onDuplicateKeyIgnore flag.

I've tried to execute batch without any strategies for duplicate key, like:

dsl.loadInto(A_B_LINK).batchAll()
                .loadRecords(records)
                .fields(A_B_LINK.fields())
                .execute();

And of course, I receive violation exceptions from time to time with such approach.

I will really appreciate any help, advice or tips related to this issue.

Upvotes: 3

Views: 4511

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

The reason for this error is that there are no fields left to update if your table consists only of fields that are part of the primary key.

There are two ways to "fix" this:

  • Add an additional column to the table. Then, that will be the column that will be updated on conflict.
  • Don't use onDuplicateKeyUpdate(), but onDuplicateKeyIgnore(). You've mentioned that in the title, but not in the code. Note that currently (as of jOOQ 3.10), the combination of batchAll() and onDuplicateKeyIgnore() is not supported, unfortunately: https://github.com/jOOQ/jOOQ/issues/7253

Upvotes: 1

Related Questions