How do you store ranges in an SQL database? By ranges I mean things like price ranges, temperature ranges, date ranges for scheduling, etc. You’d probably represent them with two columns in a table, like `min_price`

and `max_price`

, `min_temperature`

and `max_temperature`

, `start_date`

and `end_date`

. If you want to represent an unbounded range, you’d probably make one or both columns nullable and then take `NULL`

as +/- infinity.

If you want to test if a value is in a range you can use the `BETWEEN`

operator:

```
SELECT * FROM products WHERE
target_price BETWEEN min_price AND max_price;
```

This doesn’t work as nicely anymore if you work with unbounded ranges as described above. You’d have to add additional checks for NULL. What if you want to test if one of the ranges in the table overlaps with a given range?

```
SELECT * FROM products WHERE
max_given >= min_price AND
min_given <= max_price;
```

Did I make a mistake here? I’m not sure. What if they should overlap but not cover each other? And again, this becomes even more complicated with unbounded ranges.

### Enter range types

PostgreSQL has a better solution for these problems — range types. It comes with these additional built-in data 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`

You can use them as a column type in a table:

```
CREATE TABLE products (…, price_range numrange);
```

### Construction

You can construct range values for these types like this:

```
'[20,35]'::int4range
'(5,12]'::int4range
'(6.2,12.5)'::numrange
'[2022-05-01, 2022-05-31]'::daterange
'[9:30, 12:00)'::timerange
```

As you can see, they use mathematical interval notation. A square bracket means inclusive bound, and a round parenthesis means exclusive bound. They can also be unbounded (infinite) or empty:

```
'[5,)'::int4range
'(,20]'::int4range
'empty'::int4range
```

You can get the bounds of a range individually with the `lower()`

and `upper()`

functions:

```
SELECT * FROM products ORDER BY lower(price_range);
```

### Operators

The range types become really powerful through the range operators. There are a lot, so I will only show some basic examples:

- The
`&&`

operators tests if two ranges overlap:`range_a && range_b`

- The
`@>`

and`<@`

operators test if the first range contains the second or vice versa:`range_a <@ range_b`

. If used with an element on one side they test if the element is in a range:`element <@ range`

or`range @> element`

. - The
`-|-`

operator tests if two ranges are adjacent:`range_a -|- range_b`

Additionally to these boolean tests you can also calculate new ranges based on existing ranges:

The `+`

operator computes the union of two overlapping or adjacent ranges: `range_a + range_b`

. The `*`

computes the intersection of ranges, and the `-`

operator the difference.

### Multiranges

There is one more thing I want to mention: For each one of the range types there is also a *multirange* type: `int4multirange`

, `int8multirange`

, `nummultirange`

, `tsmultirange`

, `tstzmultirange`

, `datemultirange`

. As their names suggest, they store multiple ranges in one value:

```
'{}'::int4multirange
'{[2,9)}'::int4multirange
'{[2,9), [12,20)}'::int4multirange
```

The mentioned range operators work with them as well.