Search code examples
postgresqlscalamappingdoobie

WIth doobie, how do I map Scala case class to postgresql column with type tstzmultirange?

So e.g. I create the table vacations in postgres:

create table if not exists vacations (
  person text primary key,
  vacations tstzmultirange not null
)

And on the Scala side, I have:

case class Interval(start: Instant, end: Instant)
case class PersonalLeaves(person: String, onLeave: Seq[Interval])
Solution

  • PostgreSQL has a Data Type named Range

    Range types are data types representing a range of values of some element type (called the range's subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for “timestamp range”), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.

    and one of the built-in range types is

    tstzrange — Range of timestamp with time zone, tstzmultirange — corresponding Multirange

    Doobie has an extension for PostgreSQL that offer support for

    There is an open issue to add support for Range type that was open on Jul 4, 2019 and the last activity was on Jul 29, 2019.

    If you look at the unit test of postgres module for postgres types named TypeSuite, you will see the following lines where all range types are not being checked:

      // 8.17 Range Types
  skip("int4range")
  skip("int8range")
  skip("numrange")
  skip("tsrange")
  skip("tstzrange")
  skip("daterange")
  skip("custom")

    In this case, I think you will need to use the Custom Mappings that doobie has. I was able to find a gist that creates a custom mapping for tsrange which is pretty close to what you need. Also in the open issue mentioned before, there is a suggestion about how custom mapping for ranges can be done.