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 2016-06-29 1:13 GMT+02:00 Samir Faci <[email protected]>: > 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 > > -- > 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.
