"insert or replace" sugar for row "onConflict()" upsert?

[Stefan Hoffmeister]

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, 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?

[Lukas Eder]

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

--
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.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/b20fbd53-4dcb-4560-a1a4-b9524c84708bn%40googlegroups.com.

[Stefan Hoffmeister]

.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, mini.id)
    .set(Tables.MyTable.someVal, mini.someVal)
    .onConflict(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() ?

[Lukas Eder]

Yes, I know. See:

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

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/bd7f3a35-ab45-468b-ae45-12347513dc9bn%40googlegroups.com.

[Lukas Eder]

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