https://kotlinlang.org logo
#exposed
Title
# exposed
d

Dominik Sandjaja

02/21/2024, 1:51 PM
With the new "Insert Or Update" (
.upsert(){}
) functionality in exposed, is there a way to exclude a column from being updated when an update is done? Or is the only way to do so by defining a dedicated
onUpdate
statement? I was hoping of something like
onUpdateExclude: Array<Column<*>>
. My usecase: I have tables with a column like
Copy code
val createdAt = datetime("created_at").defaultExpression(CurrentDateTime)
which works fine for inserts, but I clearly don't want to update this column when I
UPDATE
the column.
c

Chantal Loncle

02/21/2024, 3:09 PM
You're correct in that, if the update block needs to differ from the insert block,
onUpdate
is currently the only option. If the difference only involves the exclusion of some columns (so all other column + value pairings stay the same), there is already a YouTrack issue requesting a way to avoid duplicating most of the insert block. Please consider upvoting that issue and including your use case above.
👍 1
a

amarcantoni

02/27/2024, 5:04 PM
Hey, is there an example or some documentation regarding
batchUpsert
for similar use cases? I would be fine with a maybe "ugly" solution that requires some code duplication, I just can't find a way to update the columns in the `onUpdate`statement with the correct values from the batch... Thank you lots!
c

Chantal Loncle

02/27/2024, 6:44 PM
Hi @amarcantoni Version 0.48.0 will have a new parameter,
onUpdateExclude
, also available to
batchUpsert()
. This can be used to update columns with the right batch values instead of duplicating from the insert block:
Copy code
object Words : Table("words") {
    val word = varchar("name", 64).uniqueIndex()
    val letters = integer("letters")
    val added = datetime("added").defaultExpression(CurrentDateTime)
}

// on conflict, do not update 'added' but use insert value for 'letters'
Words.batchUpsert(
    listOfWords,
    onUpdateExclude = listOf(Words.added)
) { word ->
    this[Words.word] = word
    this[Words.letters] = word.length
}
This Wiki section also has a new example for using
onUpdateExclude
in the reverse way (to only update specific columns using the insert batch values).
❤️ 3
a

amarcantoni

02/27/2024, 7:31 PM
Thanks a lot, super helpful! Looking forward to the new version then!