https://github.com/jOOQ/jOOQ/issues/16522 has been implemented. New methods, such as setNonPrimaryKeyToExcluded() are now available. Obviously, you can easily emulate these methods yourself by passing Field/excluded(Field) pairs to the set() clauses.
Best Regards, Lukas On Monday, November 11, 2024 at 9:10:06 AM UTC+1 Lukas Eder wrote: Yes, I know. See: https://github.com/jOOQ/jOOQ/issues/16522 On Mon, Nov 11, 2024 at 8:44 AM Stefan Hoffmeister <[email protected]> wrote: .setAllToExcluded() does not seem to behave the way I expect on DuckDB 1.1.0 with jOOQ 3.19.12. Given ``` val numRows = dsl.insertInto(Tables.MyTable) .set(Tables.MyTable.ID <http://tables.mytable.id/>, mini.id) .set(Tables.MyTable.someVal, mini.someVal) .onConflict(Tables.MyTable.ID <http://tables.mytable.id/>) *.* *setAllToExcluded()* .execute() ``` I get org.jooq.exception.DataAccessException: SQL [insert into MyTable ("id", "someVal") values (...) on conflict ("id") do update set "id" = excluded."id", "someVal" = excluded."someVal"]; java.sql.SQLException: Binder Error: Can not assign to column 'id' because it has a UNIQUE/PRIMARY KEY constraint or is referenced by an INDEX >From the SQL it is clear why the exception is generated - but I do not understand why jOOQ would generate the set "id" = excluded."id" given that this is the column listed in the onConflict() ? On Friday, November 8, 2024 at 2:10:29 PM UTC+1 [email protected] wrote: There's some syntax sugar around the excluded clause: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/insert-on-conflict-excluded/ You can call setAllToExcluded() On Fri, Nov 8, 2024 at 2:08 PM Stefan Hoffmeister <[email protected]> wrote: Is there functionality around, or some feature on the jooq roadmap which would implement "upsert" semantics for complete rows? Example: DuckDB offers nice sugar in the form of "INSERT OR REPLACE INTO" ( https://duckdb.org/docs/sql/statements/insert#insert-or-replace-into), not requiring the ON CONFLICT clause in the SQL statement. As far as I can tell, jooq could offer similar functionality in the DSL such that instead of ``` val numRows = dsl.insertInto(Tables.MyTable) .set(Tables.MyTable.ID, mini.id) .set(Tables.MyTable.someVal, mini.someVal) .onConflict(Tables.MyTable.ID) * .doUpdate() //.set(Tables.MyTable.ID <http://Tables.MyTable.ID>, mini.id <http://mini.id>) .set(Tables.MyTable.someVal, mini.someVal)* .execute() ``` it would be come possible to use ``` val numRows = dsl.insertInto(Tables.MyTable) .set(Tables.MyTable.ID, mini.id) .set(Tables.MyTable.someVal, mini.someVal) .onConflict(Tables.MyTable.ID) *.doReplace()* .execute() ``` Semantically only the columns in .set() would be touched, with the exception of the .onConflict() columns? This could make it easier to write simple row-based UPSERTs in jooq? -- 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]. To view this discussion visit https://groups.google.com/d/msgid/jooq-user/b20fbd53-4dcb-4560-a1a4-b9524c84708bn%40googlegroups.com <https://groups.google.com/d/msgid/jooq-user/b20fbd53-4dcb-4560-a1a4-b9524c84708bn%40googlegroups.com?utm_medium=email&utm_source=footer> . -- 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]. To view this discussion visit https://groups.google.com/d/msgid/jooq-user/bd7f3a35-ab45-468b-ae45-12347513dc9bn%40googlegroups.com <https://groups.google.com/d/msgid/jooq-user/bd7f3a35-ab45-468b-ae45-12347513dc9bn%40googlegroups.com?utm_medium=email&utm_source=footer> . -- 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]. To view this discussion visit https://groups.google.com/d/msgid/jooq-user/a8850c17-56cf-4e01-9989-bf03cf266b5dn%40googlegroups.com.
