postgres: cannot query column with type TIMESTAMP[] AS List<Instant>

[wreedamz]

SQLDelight Version

2.0.2

Application Operating System

postgresql, macos

Describe the Bug

I have a table

CREATE TABLE IF NOT EXISTS sleepTracker(
    patientId SERIAL PRIMARY KEY,
    wakeUpTimes TIMESTAMP[] AS List<Instant> NOT NULL
);

and an adapter

object SleepTrackerAdapter : ColumnAdapter<List<Instant>, Array<LocalDateTime>> {
    override fun decode(databaseValue: Array<LocalDateTime>) =
        databaseValue.map { it.toInstant(ZoneOffset.UTC).toKotlinInstant() }

    override fun encode(value: List<Instant>): Array<LocalDateTime> {
        return value.map { it.toJavaInstant() }
            .map { LocalDateTime.ofInstant(it, ZoneOffset.UTC) }
            .toTypedArray()
    }
}

When I query the database via sqldelight with

SELECT * FROM sleepTracker;

I get

Exception in thread "main" java.lang.ClassCastException: class [Ljava.sql.Timestamp; cannot be cast to class [Ljava.time.LocalDateTime; ([Ljava.sql.Timestamp; is in module java.sql of loader 'platform'; [Ljava.time.LocalDateTime; is in module java.base of loader 'bootstrap')
	at com.foo.SleepTrackerAdapter.decode(Main.kt:21)
	at com.foo.SleepTrackerQueries$getAllTracker$1.invoke(SleepTrackerQueries.kt:25)
	at com.foo.SleepTrackerQueries$getAllTracker$1.invoke(SleepTrackerQueries.kt:19)
	at app.cash.sqldelight.ExecutableQuery$executeAsList$1.invoke(Query.kt:176)
	at app.cash.sqldelight.ExecutableQuery$executeAsList$1.invoke(Query.kt:174)
	at app.cash.sqldelight.driver.jdbc.JdbcPreparedStatement.executeQuery(JdbcDriver.kt:283)
	at app.cash.sqldelight.driver.jdbc.JdbcDriver.executeQuery(JdbcDriver.kt:155)
	at app.cash.sqldelight.SimpleQuery.execute(Query.kt:98)
	at app.cash.sqldelight.ExecutableQuery.executeAsList(Query.kt:174)
	at com.foo.MainKt.main(Main.kt:18)
	at com.foo.MainKt.main(Main.kt)

Repository reproducing issue

[griffio]

Sadly, I think this is an issue with PostgreSql JDBC see https://github.com/pgjdbc/pgjdbc/issues/1225 Currently the JDBC driver has no support for Array<Timestamp> to Array<LocalDateTime> - it does for single column types Timestamp to LocalDateTime

Possible work-around - use a manual mapper to unpack the Array<Timestamp> (note the SleepTrackerAdapter adapter is not used except to generate the Instant field on the data table)

e.g

val mapper: (SqlCursor) -> SleepTracker = { cursor ->
        check(cursor is JdbcCursor)
        val patientId: Int = cursor.getInt(0)!!
        val times = cursor.getArray<Timestamp>(1)!!
        SleepTracker(patientId, times.map { it.toInstant() })
    }

    val qry = object : ExecutableQuery<SleepTracker>(mapper) {
        override fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R> {
            return driver.executeQuery(-1, "SELECT * FROM sleepTracker", mapper, 0) {}
        }
    }
    println(qry.executeAsList())

[griffio]

I should also add that support for PostgreSql tsrange/tstzrange as an alternative data type for storing temporal values is still in PR stage https://github.com/sqldelight/sqldelight/pull/5297.