Hi everyone :wave: I'm having trouble with an`INS...

# exposed

[Jonas]

Hi everyone 👋 I'm having trouble with an`INSERT ... ON CONFLICT UPDATE`. Consider the following tables:

object DevicesTable : LongIdTable("devices") {
    val deviceId = uuid("device_id").uniqueIndex()
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp)
    val modifiedAt = timestamp("modified_at").defaultExpression(CurrentTimestamp)

    val leaderboard = reference("leaderboard_id", LeaderboardsTable.id)
}

and

object LeaderboardsTable : LongIdTable("leaderboards") {
    val key = varchar("key", length = 40).uniqueIndex()
    val title = varchar("title", length = 32)
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp)
    val modifiedAt = timestamp("modified_at").defaultExpression(CurrentTimestamp)
}

I'm trying this:

transaction(db) {
    val leaderboard =
        LeaderboardEntity.find { LeaderboardsTable.key eq leaderboardKey }.firstOrNull()
    if (leaderboard == null) {
        return@transaction null
    }

    DevicesTable.upsert(onUpdate = {
        it[DevicesTable.leaderboard] = leaderboard.id
        it[DevicesTable.modifiedAt] = Clock.System.now()
    }) {
        it[DevicesTable.deviceId] = UUID.fromString(deviceId)
        it[DevicesTable.leaderboard] = leaderboard.id
    }

}

The error looks like this:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "S.ID" not found; SQL statement:
MERGE INTO DEVICES T USING (VALUES (?, ?)) S(DEVICE_ID, LEADERBOARD_ID) ON (T.ID=S.ID) WHEN MATCHED THEN UPDATE SET T.LEADERBOARD_ID=?, T.MODIFIED_AT=? WHEN NOT MATCHED THEN INSERT (DEVICE_ID, LEADERBOARD_ID) VALUES(S.DEVICE_ID, S.LEADERBOARD_ID) [42122-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:244)
	at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:226)
	at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213)
	at org.h2.expression.condition.Comparison.optimize(Comparison.java:148)

I'm using the Postgres Driver with Hikari and the newest version of exposed.