Jooq Postgres 9.5+ Upsert Usage

[Samir Faci]

I was wondering what the status of upsert support is in Jooq.

Please correct me if I'm wrong, but it sounds like there's 2 syntax for dealing with upserts.

1.  http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/merge-statement/  (which I don't think is supported for postgres ) 

Example:

create.mergeInto(AUTHOR)
      .using(create().selectOne())
      .on(AUTHOR.LAST_NAME.equal("Hitchcock"))
      .whenMatchedThenUpdate()
      .set(AUTHOR.FIRST_NAME, "John")
      .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

​

2.  I also noticed this ticket:

https://github.com/jOOQ/jOOQ/issues/5166  which mentions this pattern:

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())))

It doesn't seem like either approach is bug free but I was wondering if there was a right way of doing upserts and what state it's in.  ie. is it functional?

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Lukas Eder]

Hi Samir,

Indeed, very unfortunately, PostgreSQL decided against implementing the SQL standard MERGE statement and implemented their own, vendor-specific alternative. One of the very few cases where PostgreSQL goes against the standard.

Thus, you cannot use jOOQ's MERGE statement.

Both MySQL's ON DUPLICATE KEY UPDATE clause (your example 2) and H2's MERGE statement can be emulated in PostgreSQL, though, at least if the primary key is known to jOOQ (e.g. when using the code generator). The upcoming jOOQ 3.9 will also add native support for PostgreSQL's ON CONFLICT clause:

https://github.com/jOOQ/jOOQ/issues/5297

Another alternative is to use plain SQL templating and add the ON CONFLICT clause in string form after the INSERT statement.

That's the current state.

Hope this helps,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.