Using Postgres range data type in Ecto

Since PostgreSQL 9.2 we have the ability to store and query range of values with specific operators and functions. This is a super nice feature that removes complexity when handling logic around two pieces of information that can also be representing a third one.

Let's say that we have a model that has price_range as one of its attributes. Instead of handling two fields like minimum_price and maximum_price, we can use ranges of age like [0, 45.67], [30.04, 98.50], [100, 500] and so one. This way, we can perform queries using range operators that will match things like "2.5 <@ numrange(1.50,7)" that means element 2.5 is contained by range from 1.50 to 7, what is true in this example.

PostgreSQL comes with the following built-in range types:

int4range — Range of integer

int8range — Range of bigint

numrange — Range of numeric

tsrange — Range of timestamp without time zone

tstzrange — Range of timestamp with time zone

daterange — Range of date

Based on the fact that Ecto has a PostgreSQL adapter called Postgrex, it should be very trivial to take advantage of the range data type, like any other common data type as string, integer or boolean. However, that is not true as Ecto doesn't provide an out-of-box Range data type. The good news is that Ecto allow you to create custom data types when needed that will match the adapter data type available, and it is really simple to do it.

Postgrex comes with a data type %Postgrex.Range{}, this will be our reference in our custom Ecto data type. The %Postgrex.Range{} accepts 4 attributes: lower, upper, lower_inclusive, upper_inclusive (both lower and upper inclusive attributes are set as true by default) that pretty much maps with how Postgres sets ranges.

The load/1 function will receive from the adapter a struct called %Postgrex.Range{}. Notice that depending on the case we can have ranges using infinity (passing nil as value), so we can pattern match those cases. The return is a tuple with the second element being a list of 2 values.

Also, any transformation can happen in this stage, in the case below I am converting to float the value the adapter sends, that is a Decimal.