Range types are a big differentiator of postgres, and they are awesome. Range types allows storing two dimensions of data in a single column. Many data modeling possibilities come true after combining them with the unique and constraint indexes that can be added.

Screenshot from Postgres 10 documentation, showing different range types available.

1. Financial Instruments pricing

These range types can be used off the shelf when using postgres as the primary database. For an application developer, range types naturally yield to the real world applications. For example, if you want to store price of any financial instrument, the price has a validity period associated with it. This report from IMF shows currency data for fiats and for cryptoassets the pricing can be sourced from coinmarketcap’s historical table. Following datamodel can support this data.

create table currency_rates

(

symbol text,

validity daterange,

rate numeric

);

2. Engineering values with tolerance

There are many engineering values/metrics which are not exact values but are numbers with a tolerance example acceptable torque wrench accuracy in mechanical engineering. Most of the numbers associated with analog machines occur in a range, postgres integer range or numrange works perfectly for such scenarios.

3. Budgets and time scheduling

Modeling any negotiable quantity like a timeline or budget can be best modeled over an acceptable range than a single value. As we will see later availability of uniques and overlap constraints on ranges allow you to model the real life constraints in database and not in your application. A machine’s availability for accepting new work in a time period does not belong to application logic but belongs to a hard core database constraint. Considering concurrency issues and race condition possibilities in application code, database constraints are much more reliable for real world constraints.