Spring's R2DBC `DatabaseClient`: How to insert with `tstzrange` fields

Question

Basically, in my WebFlux application, I have a Spring R2DBC's DatabaseClient, with which I am trying to insert into a table containing a row typed as tstzrange. When trying to insert a String into this row, I get the following error:

[42804] column "lifetime" is of type tstzrange but expression is of type character varying

client.sql("""
               update article
               set lifetime = :lifetime
               """)
      .bind("lifetime", range.toString()))

This seems "normal", but I can not find documentation on how to write my own Parameter which can encode my String into a tstzrange.

Note: inserting using the same String without bind parameters works.

Where can I find documentation on how to implement such feature?

Or did someone already write an adapter for tstzrange in Spring's R2DBC DatabaseClient?

score 0 · Accepted Answer · answered Jul 17 '23 at 13:09

Solved using the following "trick":

client.sql("""
               update article
               set lifetime = tstzrange(:lower::timestamptz, :upper::timestamptz, :bounds)
               """)
      .bind("lower", Parameter.fromOrEmpty(range.getLowerString(), String.class))
      .bind("upper", Parameter.fromOrEmpty(range.getUpperString(), String.class))
      .bind("bounds", Parameter.fromOrEmpty(range.getBoundsString(), String.class))

Not really a huge fan of this, I wish I would not have to add complexity in such a supposedly simple task, but at least it works.

Spring's R2DBC `DatabaseClient`: How to insert with `tstzrange` fields

1 Answers1