uPg: Range types

PostgreSQL provides a facility to represent intervals of values. They can be bounded (have both ends), e.g [0, 1], [2, 10), unbounded (at least one end is infinity or absent), e.g. (-∞, +∞), and empty.

The range that can be unbounded is modelled by storages::postgres::Range<> template, which provides means of constructing any possible combination of interval ends. It is very versatile, but not very convenient in most cases. storages::postgres::BoundedRange<> template is an utility that works only with bounded ranges.

PostgreSQL Built-in Range Datatypes

Some of PostgreSQL built-in range datatypes (https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN) are supported by the library, please see uPg: Supported data types

User Range Types

A user can define custom range types (https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-DEFINING) and they can be mapped to C++ counterparts, e.g.

CREATE TYPE my_range AS RANGE (
  subtype = my_type
);

and declare a mapping from C++ range to this type just as for any other user type:

template<>
struct CppToUserPg<Range<my_type>> {
  static constexpr DBTypeName postgres_name = "my_type";
};

Please note that my_type must be comparable both in Postgres and in C++

See uPg: Mapping a C++ type to PostgreSQL user type for more info

Time Range and Other Widely Used Types

If you need a range of PostgreSQL float type or time type (actually any type mapped to C++ type that is highly likely used by other developers), please DO NOT specialize mapping for Range<float>, Range<double> or Range<TimeOfDay<seconds>>. Please declare a strong typedef for your range or bounded range and map it to your postgres range type.

Here is an example how to define a strong typedef for a range of TimeOfDay and map it to postgres user range type.

namespace pgtest {
 
template <typename Duration>
using TimeRange =
    utils::StrongTypedef<struct MyTimeTag,
                         pg::Range<utils::datetime::TimeOfDay<Duration>>>;
 
template <typename Duration>
using BoundedTimeRange = utils::StrongTypedef<
    struct MyTimeTag, pg::BoundedRange<utils::datetime::TimeOfDay<Duration>>>;
 
}  // namespace pgtest

namespace storages::postgres::io {
 
template <typename Duration>
struct CppToUserPg<pgtest::TimeRange<Duration>> {
  static constexpr DBTypeName postgres_name = kRangeName;
};
 
template <typename Duration>
struct CppToUserPg<pgtest::BoundedTimeRange<Duration>> {
  static constexpr DBTypeName postgres_name = kRangeName;
};
 
}  // namespace storages::postgres::io

---

⇦ uPg: Mapping a C++ enum to PostgreSQL enum type. | uPg: Arrays ⇨