Actually, the the second approach does work, though I was running into a
different issue because the "primary key" in my case is serial ID which
doesn't work well with upserts.
I was wondering if there was a way to explicitly list the columns I want to
use for upserts.
Example:
insert into pinned_tweets (user_handle, tweet_id, pinned_at)
values (
'rey',
1,
clock_timestamp()
)on conflict (user_handle)do update set (tweet_id, pinned_at) = (1,
clock_timestamp())where pinned_tweets.user_handle = 'rey';
(Pulled from a random blog)
But I believe any valid Index or constrain can be applied (Postgres). Can
we add a method that takes a .onConflict(Index_name or constraint_name) ?
I'm assuming jooq can auto-gen indexes as well?
Thanks for the issue listed, I'll watch that and can't wait to see it get
resolved.
--
Samir Faci
On Mon, Oct 31, 2016 at 7:00 AM, Lukas Eder <[email protected]> wrote:
> Hi Samir,
>
> I'm sorry for the delay.
>
> dsl.batchInsert(records).onDuplicateKeyUpdate()
>> .set(Table.Field, TableRecord.getField)
>> .set(Table.AnotherField, TableRecord.getAnotherField)
>> .execute();
>
>
> Currently, this isn't possible out of the box. There's a pending feature
> request for this, here:
> https://github.com/jOOQ/jOOQ/issues/3172
>
> But you should be able to batch your explicit INSERT .. ON DUPLICATE KEY
> UPDATE statements
>
> Either ways, my wish list aside, neither pattern seems to work with Jooq
>> 3.8.1 (Or I'm passing in the wrong data type). Is there a supported
>> pattern that does work?
>> Or can upserts not be done in conjunction with batch operations?
>
>
> What went wrong?
>
> Lukas
>
> 2016-10-20 3:34 GMT+02:00 Samir Faci <[email protected]>:
>
>> Just for reference, simple upsert work fine, using something along these
>> lines. Snippet below is my preferred pattern, mergeInto is less verbose,
>> but our PKeys don't actually mean primary key at times so being explicit is
>> necessary.
>>
>> this.ctx().insertInto(this.table())
>> .set(DSL.field("id"), f.id())
>> .set(DSL.field("last_modified"), Timestamp.from(Instant.now()))
>> * .onDuplicateKeyUpdate()*
>> .set(DSL.field("last_modified"), Timestamp.from(Instant.now())))
>>
>>
>> What I'm trying to do is take advantage of postgres upsert operation and
>> use batch operations.
>>
>> Ideally I would love something along these lines to work.
>>
>> List<SomeTableRecord> records = Lists.newArrayList();
>> // build up a list of recrods...
>>
>>
>> dsl.batchInsert(records).onDuplicateKeyUpdate()
>> .set(Table.Field, TableRecord.getField)
>> .set(Table.AnotherField, TableRecord.getAnotherField)
>> .execute();
>>
>> Failing that, is there support for batch operation with upserts?
>>
>> I was also trying to use this pattern:
>>
>> create.batch(
>> create.insertInto(AUTHOR, ID, FIRST_NAME, LAST_NAME).values(1, "Erich"
>> , "Gamma" ),
>> create.insertInto(AUTHOR, ID, FIRST_NAME, LAST_NAME).values(2,
>> "Richard", "Helm" ),
>> create.insertInto(AUTHOR, ID, FIRST_NAME, LAST_NAME).values(3, "Ralph"
>> , "Johnson" ),
>> create.insertInto(AUTHOR, ID, FIRST_NAME, LAST_NAME).values(4, "John"
>> , "Vlissides")).execute();
>>
>>
>>
>> It would be great if I can write something along the lines of:
>>
>> List<InsertOnDuplicateSetMoreStep<TableRecord>> queries =
>> Lists.newArrayList();
>>
>> InsertOnDuplicateSetMoreStep<TableRecord> query =
>> this.ctx().insertInto(this.table())
>>
>> .set(DSL.field("id"), f.id())
>> .set(DSL.field("last_modified"), Timestamp.from(Instant.now()))
>> * .onDuplicateKeyUpdate()*
>> .set(DSL.field("last_modified"), Timestamp.from(Instant.now())))
>>
>> queries.add(query);
>>
>> Build up a List of these query and then simply execute something along
>> the lines of:
>>
>> create.batch( queryList).execute();
>>
>>
>>
>> Either ways, my wish list aside, neither pattern seems to work with Jooq
>> 3.8.1 (Or I'm passing in the wrong data type). Is there a supported
>> pattern that does work?
>> Or can upserts not be done in conjunction with batch operations?
>>
>>
>> --
>> Thank you
>> Samir Faci
>> https://keybase.io/csgeek
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
Thank you
Samir Faci
https://keybase.io/csgeek
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
