PostgreSQL: Documentation: 18: 9.20. Range/Multirange Functions and Operators

Documentation → PostgreSQL 18

Supported Versions: Current (18) / 17 / 16 / 15 / 14

Development Versions: 19 / devel

Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2

9.20. Range/Multirange Functions and Operators
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.20. Range/Multirange Functions and Operators #

See Section 8.17 for an overview of range types.

Table 9.58 shows the specialized operators available for range types. Table 9.59 shows the specialized operators available for multirange types. In addition to those, the usual comparison operators shown in Table 9.1 are available for range and multirange types. The comparison operators order first by the range lower bounds, and only if those are equal do they compare the upper bounds. The multirange operators compare each range until one is unequal. This does not usually result in a useful overall ordering, but the operators are provided to allow unique indexes to be constructed on ranges.

Table 9.58. Range Operators

Operator Description Example(s)
`anyrange` `@>` `anyrange` → `boolean` Does the first range contain the second? `int4range(2,4) @> int4range(2,3)` → `t`
`anyrange` `@>` `anyelement` → `boolean` Does the range contain the element? `'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp` → `t`
`anyrange` `<@` `anyrange` → `boolean` Is the first range contained by the second? `int4range(2,4) <@ int4range(1,7)` → `t`
`anyelement` `<@` `anyrange` → `boolean` Is the element contained in the range? `42 <@ int4range(1,7)` → `f`
`anyrange` `&&` `anyrange` → `boolean` Do the ranges overlap, that is, have any elements in common? `int8range(3,7) && int8range(4,12)` → `t`
`anyrange` `<<` `anyrange` → `boolean` Is the first range strictly left of the second? `int8range(1,10) << int8range(100,110)` → `t`
`anyrange` `>>` `anyrange` → `boolean` Is the first range strictly right of the second? `int8range(50,60) >> int8range(20,30)` → `t`
`anyrange` `&<` `anyrange` → `boolean` Does the first range not extend to the right of the second? `int8range(1,20) &< int8range(18,20)` → `t`
`anyrange` `&>` `anyrange` → `boolean` Does the first range not extend to the left of the second? `int8range(7,20) &> int8range(5,10)` → `t`
`anyrange` `-\|-` `anyrange` → `boolean` Are the ranges adjacent? `numrange(1.1,2.2) -\|- numrange(2.2,3.3)` → `t`
`anyrange` `+` `anyrange` → `anyrange` Computes the union of the ranges. The ranges must overlap or be adjacent, so that the union is a single range (but see `range_merge()`). `numrange(5,15) + numrange(10,20)` → `[5,20)`
`anyrange` `` `anyrange` → `anyrange` Computes the intersection of the ranges. `int8range(5,15) int8range(10,20)` → `[10,15)`
`anyrange` `-` `anyrange` → `anyrange` Computes the difference of the ranges. The second range must not be contained in the first in such a way that the difference would not be a single range. `int8range(5,15) - int8range(10,20)` → `[5,10)`

Operator

Description

Example(s)

anyrange @> anyrange → boolean

Does the first range contain the second?

int4range(2,4) @> int4range(2,3) → t

anyrange @> anyelement → boolean

Does the range contain the element?

'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp → t

anyrange <@ anyrange → boolean

Is the first range contained by the second?

int4range(2,4) <@ int4range(1,7) → t

anyelement <@ anyrange → boolean

Is the element contained in the range?

42 <@ int4range(1,7) → f

anyrange && anyrange → boolean

Do the ranges overlap, that is, have any elements in common?

int8range(3,7) && int8range(4,12) → t

anyrange << anyrange → boolean

Is the first range strictly left of the second?

int8range(1,10) << int8range(100,110) → t

anyrange >> anyrange → boolean

Is the first range strictly right of the second?

int8range(50,60) >> int8range(20,30) → t

anyrange &< anyrange → boolean

Does the first range not extend to the right of the second?

int8range(1,20) &< int8range(18,20) → t

anyrange &> anyrange → boolean

Does the first range not extend to the left of the second?

int8range(7,20) &> int8range(5,10) → t

anyrange -|- anyrange → boolean

Are the ranges adjacent?

numrange(1.1,2.2) -|- numrange(2.2,3.3) → t

anyrange + anyrange → anyrange

Computes the union of the ranges. The ranges must overlap or be adjacent, so that the union is a single range (but see range_merge()).

numrange(5,15) + numrange(10,20) → [5,20)

anyrange * anyrange → anyrange

Computes the intersection of the ranges.

int8range(5,15) * int8range(10,20) → [10,15)

anyrange - anyrange → anyrange

Computes the difference of the ranges. The second range must not be contained in the first in such a way that the difference would not be a single range.

int8range(5,15) - int8range(10,20) → [5,10)

Table 9.59. Multirange Operators

Operator Description Example(s)
`anymultirange` `@>` `anymultirange` → `boolean` Does the first multirange contain the second? `'{[2,4)}'::int4multirange @> '{[2,3)}'::int4multirange` → `t`
`anymultirange` `@>` `anyrange` → `boolean` Does the multirange contain the range? `'{[2,4)}'::int4multirange @> int4range(2,3)` → `t`
`anymultirange` `@>` `anyelement` → `boolean` Does the multirange contain the element? `'{[2011-01-01,2011-03-01)}'::tsmultirange @> '2011-01-10'::timestamp` → `t`
`anyrange` `@>` `anymultirange` → `boolean` Does the range contain the multirange? `'[2,4)'::int4range @> '{[2,3)}'::int4multirange` → `t`
`anymultirange` `<@` `anymultirange` → `boolean` Is the first multirange contained by the second? `'{[2,4)}'::int4multirange <@ '{[1,7)}'::int4multirange` → `t`
`anymultirange` `<@` `anyrange` → `boolean` Is the multirange contained by the range? `'{[2,4)}'::int4multirange <@ int4range(1,7)` → `t`
`anyrange` `<@` `anymultirange` → `boolean` Is the range contained by the multirange? `int4range(2,4) <@ '{[1,7)}'::int4multirange` → `t`
`anyelement` `<@` `anymultirange` → `boolean` Is the element contained by the multirange? `4 <@ '{[1,7)}'::int4multirange` → `t`
`anymultirange` `&&` `anymultirange` → `boolean` Do the multiranges overlap, that is, have any elements in common? `'{[3,7)}'::int8multirange && '{[4,12)}'::int8multirange` → `t`

Operator

Description

Example(s)

anymultirange @> anymultirange → boolean

Does the first multirange contain the second?

'{[2,4)}'::int4multirange @> '{[2,3)}'::int4multirange → t

anymultirange @> anyrange → boolean

Does the multirange contain the range?

'{[2,4)}'::int4multirange @> int4range(2,3) → t

anymultirange @> anyelement → boolean

Does the multirange contain the element?

'{[2011-01-01,2011-03-01)}'::tsmultirange @> '2011-01-10'::timestamp → t

anyrange @> anymultirange → boolean

Does the range contain the multirange?

'[2,4)'::int4range @> '{[2,3)}'::int4multirange → t

anymultirange <@ anymultirange → boolean

Is the first multirange contained by the second?

'{[2,4)}'::int4multirange <@ '{[1,7)}'::int4multirange → t

anymultirange <@ anyrange → boolean

Is the multirange contained by the range?

'{[2,4)}'::int4multirange <@ int4range(1,7) → t

anyrange <@ anymultirange → boolean

Is the range contained by the multirange?

int4range(2,4) <@ '{[1,7)}'::int4multirange → t

anyelement <@ anymultirange → boolean

Is the element contained by the multirange?

4 <@ '{[1,7)}'::int4multirange → t

anymultirange && anymultirange → boolean

Do the multiranges overlap, that is, have any elements in common?

'{[3,7)}'::int8multirange && '{[4,12)}'::int8multirange → t