Support range types for Postgres

[rsromanowski]

Postgres has many built-in range types for fields: https://www.postgresql.org/docs/current/rangetypes.html

PostgreSQL comes with the following built-in range types:

• int4range — Range of integer
• int8range — Range of bigint
• numrange — Range of numeric
• tsrange — Range of timestamp without time zone
• tstzrange — Range of timestamp with time zone
• daterange — Range of date

[OlivierCavadenti]

I will take a look of this issue soon.

[pratikdotexe]

@OlivierCavadenti Did you take a look?

[philipchang-RL]

@OlivierCavadenti Was there ever a resolution for these supported range types in exposed? I tried implementing my own custom column types in exposed to support tstzrange however, the exposed JDBC driver complains when trying to insert a tstzrange formatted string into the tstzrange formatted column. Here is an example of the error: ERROR: column "_valid_period" is of type tstzrange but expression is of type character varying

[bog-walk]

Hi @philipchang-RL The casting exception you're seeing is most likely because you need to also override ColumnType.setParameter(). When setting an object in a prepared statement with JDBC, any unknown data type without a JDBC mapping is set as a varchar string. With PostgreSQL's stricter type system, this means needing to manually declare what the type of the set parameter should be, using a PGobject.

Here's a more detailed explanation of the error if you're interested.

Using current version 0.50.0, here's an example of how to implement a custom range type, specifically an int4range column that accepts IntRange input values:

class IntRangeColumnType : ColumnType<IntRange>() {
    override fun sqlType(): String = "int4range"

    override fun nonNullValueToString(value: IntRange): String = "[${value.first},${value.last}]"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = nonNullValueToString(it as IntRange)
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }

    override fun valueFromDB(value: Any): IntRange? = when (value) {
        is PGobject -> value.value?.let {
            val components = it.trim('[', ')').split(',')
            IntRange(components.first().toInt(), components.last().toInt() - 1)
        }
        else -> error("Retrieved unexpected value of type ${value::class.simpleName}")
    }
}

fun Table.intRange(name: String): Column<IntRange> = registerColumn(name, IntRangeColumnType())

// and how to use it
object Tester : Table("tester") {
    val amounts = intRange("amounts")
}

Tester.insert {
    it[amounts] = 10..<100
}