With the new "<Insert Or Update>" (`.upsert(){}` )...

# exposed

[Dominik Sandjaja]

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

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.

[Chantal Loncle]

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.

[amarcantoni]

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!

[Chantal Loncle]

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:

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

[amarcantoni]

Thanks a lot, super helpful!
Looking forward to the new version then!