Regina Obe: Multirange types in PostgreSQL 14

One of the features we are most excited about in recently released PostgreSQL 14 is the introduction of Multirange types.
In a nutshell Multirange types are sets of non-overlapping ranges. Unlike array of ranges, they prevent overlaps and thus
allow you to effectively model ranges with gaps.

One of the use cases we have for them is modeling time. For example if you wanted to keep track of the cumulative periods and how many days someone is in a hospital, you could store this as a datemultirange type.

There are quite a few operators and functions available in PostgreSQL 14, but some glaring ones we’d need include aggregates such as a union aggregate. That currently doesn’t exist. There are however your standard operators like + (union two ranges) and * for intersection, and – for difference as well as your common containment boolean operators.

Continue reading “Multirange types in PostgreSQL 14”