`io.r2dbc.spi.Parameter` not considered a simple type

[rsmidt]

I'm currently struggling to use a simple tsrange in a template entity insert.

Reading is fine, as I can utilize a simple Converter<String, MyTsRangeType>. But doing the same for writing (Converter<MyTsRangeType, String>) does not work:

column "my_column" is of type tsrange but expression is of type character varying

Is there a proper way for dealing with ranges?

[mp911de]

You need to apply casting on the SQL level. Alternatively, you can provide a converter from MyTsRangeType to Parameter if you can provide the Postgres Type OID.

The driver doesn't provide any value objects for ranges, therefore you must bind the value as string and tell the driver to use tsrange

R2DBC's Parameters.in(…) along with the driver's PostgresTypes.lookupType("tsrange") should return you the type descriptor required for Parameters.in(myTsRangeType.toString(), typeDesc).

Let me know whether this helps.

[rsmidt]

Hey @mp911de, thanks for your swift reply.

Ideally, I could avoid doing it on the SQL level and fully rely on the abstractions already there. Following your feedback, I tried the following (excuse the Kotlin):

// Taken from PostgresTypes.from(...).lookupType("tsrange")
private val PG_RANGE_TYPE = PostgresTypes.PostgresType(3908, 3908, 3909, 3909, "tsrange", "R")

@WritingConverter
object TsRangeSerializer : Converter<TsRange, Parameter> {
    override fun convert(source: TsRange): Parameter {
        return Parameters.`in`(PG_RANGE_TYPE, source.toPostgresValue())
    }
}

fun TsRange.toPostgresValue(): String =
    "[\"${start.format(PG_TIMESTAMP_FORMATTER)}\",\"${end.format(PG_TIMESTAMP_FORMATTER)}\"]"

Unfortunately, now it fails with an error I don't fully comprehend:

java.lang.IllegalArgumentException: Cannot encode null parameter of type io.r2dbc.spi.Parameters$InParameter
at io.r2dbc.postgresql.codec.DefaultCodecs.encodeNull(DefaultCodecs.java:302)

I don't understand why it tries to bind to null?

[mp911de]

null values are not using the Converter, instead, we use the property (converted) type and not a value.

You could register a BeforeSaveCallback and post-process the OutboundRow to replace the value with a proper Parameters.in(…) null value wrapped in a Spring org.springframework.r2dbc.core.Parameter.

[rsmidt]

Maybe I'm misunderstanding something, but the value is not null in this case. The column itself is actually not nullable. That's why I expect it will never try to bind anything nullish.

[mp911de]

Can you provide the full stack trace and a minimal sample (SQL, domain types, converters, test)? Happy to debug the issue here once I have a bit of code that gets me started.

[rsmidt]

Thank you! Here's the repo: https://github.com/rsmidt/spring-data-r2dbc-range-types

It's written in Kotlin. I can change it to Java if required. It's expecting Postgres running on port 5432, configured in the application.properties (there's also a docker-compose.yml).

Please let me know if I can help in any way.