<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.19. Range Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-array.html" title="9.18. Array Functions and Operators" /><link rel="next" href="functions-aggregate.html" title="9.20. Aggregate Functions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.19. Range Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-array.html" title="9.18. Array Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-aggregate.html" title="9.20. Aggregate Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-RANGE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.19. Range Functions and Operators</h2></div></div></div><p>
See <a class="xref" href="rangetypes.html" title="8.17. Range Types">Section 8.17</a> for an overview of range types.
</p><p>
<a class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.50. Range Operators">Table 9.50</a> shows the operators
available for range types.
</p><div class="table" id="RANGE-OPERATORS-TABLE"><p class="title"><strong>Table 9.50. Range Operators</strong></p><div class="table-contents"><table class="table" summary="Range Operators" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <code class="literal">=</code> </td><td>equal</td><td><code class="literal">int4range(1,5) = '[1,4]'::int4range</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><></code> </td><td>not equal</td><td><code class="literal">numrange(1.1,2.2) <> numrange(1.1,2.3)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><</code> </td><td>less than</td><td><code class="literal">int4range(1,10) < int4range(2,3)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">></code> </td><td>greater than</td><td><code class="literal">int4range(1,10) > int4range(1,5)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><=</code> </td><td>less than or equal</td><td><code class="literal">numrange(1.1,2.2) <= numrange(1.1,2.2)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">>=</code> </td><td>greater than or equal</td><td><code class="literal">numrange(1.1,2.2) >= numrange(1.1,2.0)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">@></code> </td><td>contains range</td><td><code class="literal">int4range(2,4) @> int4range(2,3)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">@></code> </td><td>contains element</td><td><code class="literal">'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><@</code> </td><td>range is contained by</td><td><code class="literal">int4range(2,4) <@ int4range(1,7)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><@</code> </td><td>element is contained by</td><td><code class="literal">42 <@ int4range(1,7)</code></td><td><code class="literal">f</code></td></tr><tr><td> <code class="literal">&&</code> </td><td>overlap (have points in common)</td><td><code class="literal">int8range(3,7) && int8range(4,12)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><<</code> </td><td>strictly left of</td><td><code class="literal">int8range(1,10) << int8range(100,110)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">>></code> </td><td>strictly right of</td><td><code class="literal">int8range(50,60) >> int8range(20,30)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">&<</code> </td><td>does not extend to the right of</td><td><code class="literal">int8range(1,20) &< int8range(18,20)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">&></code> </td><td>does not extend to the left of</td><td><code class="literal">int8range(7,20) &> int8range(5,10)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">-|-</code> </td><td>is adjacent to</td><td><code class="literal">numrange(1.1,2.2) -|- numrange(2.2,3.3)</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">+</code> </td><td>union</td><td><code class="literal">numrange(5,15) + numrange(10,20)</code></td><td><code class="literal">[5,20)</code></td></tr><tr><td> <code class="literal">*</code> </td><td>intersection</td><td><code class="literal">int8range(5,15) * int8range(10,20)</code></td><td><code class="literal">[10,15)</code></td></tr><tr><td> <code class="literal">-</code> </td><td>difference</td><td><code class="literal">int8range(5,15) - int8range(10,20)</code></td><td><code class="literal">[5,10)</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
The simple comparison operators <code class="literal"><</code>,
<code class="literal">></code>, <code class="literal"><=</code>, and
<code class="literal">>=</code> compare the lower bounds first, and only if those
are equal, compare the upper bounds. These comparisons are not usually
very useful for ranges, but are provided to allow B-tree indexes to be
constructed on ranges.
</p><p>
The left-of/right-of/adjacent operators always return false when an empty
range is involved; that is, an empty range is not considered to be either
before or after any other range.
</p><p>
The union and difference operators will fail if the resulting range would
need to contain two disjoint sub-ranges, as such a range cannot be
represented.
</p><p>
<a class="xref" href="functions-range.html#RANGE-FUNCTIONS-TABLE" title="Table 9.51. Range Functions">Table 9.51</a> shows the functions
available for use with range types.
</p><a id="id-1.5.8.24.9" class="indexterm"></a><a id="id-1.5.8.24.10" class="indexterm"></a><a id="id-1.5.8.24.11" class="indexterm"></a><a id="id-1.5.8.24.12" class="indexterm"></a><a id="id-1.5.8.24.13" class="indexterm"></a><a id="id-1.5.8.24.14" class="indexterm"></a><a id="id-1.5.8.24.15" class="indexterm"></a><div class="table" id="RANGE-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.51. Range Functions</strong></p><div class="table-contents"><table class="table" summary="Range Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>
<code class="literal">
<code class="function">lower</code>(<code class="type">anyrange</code>)
</code>
</td><td>range's element type</td><td>lower bound of range</td><td><code class="literal">lower(numrange(1.1,2.2))</code></td><td><code class="literal">1.1</code></td></tr><tr><td>
<code class="literal">
<code class="function">upper</code>(<code class="type">anyrange</code>)
</code>
</td><td>range's element type</td><td>upper bound of range</td><td><code class="literal">upper(numrange(1.1,2.2))</code></td><td><code class="literal">2.2</code></td></tr><tr><td>
<code class="literal">
<code class="function">isempty</code>(<code class="type">anyrange</code>)
</code>
</td><td><code class="type">boolean</code></td><td>is the range empty?</td><td><code class="literal">isempty(numrange(1.1,2.2))</code></td><td><code class="literal">false</code></td></tr><tr><td>
<code class="literal">
<code class="function">lower_inc</code>(<code class="type">anyrange</code>)
</code>
</td><td><code class="type">boolean</code></td><td>is the lower bound inclusive?</td><td><code class="literal">lower_inc(numrange(1.1,2.2))</code></td><td><code class="literal">true</code></td></tr><tr><td>
<code class="literal">
<code class="function">upper_inc</code>(<code class="type">anyrange</code>)
</code>
</td><td><code class="type">boolean</code></td><td>is the upper bound inclusive?</td><td><code class="literal">upper_inc(numrange(1.1,2.2))</code></td><td><code class="literal">false</code></td></tr><tr><td>
<code class="literal">
<code class="function">lower_inf</code>(<code class="type">anyrange</code>)
</code>
</td><td><code class="type">boolean</code></td><td>is the lower bound infinite?</td><td><code class="literal">lower_inf('(,)'::daterange)</code></td><td><code class="literal">true</code></td></tr><tr><td>
<code class="literal">
<code class="function">upper_inf</code>(<code class="type">anyrange</code>)
</code>
</td><td><code class="type">boolean</code></td><td>is the upper bound infinite?</td><td><code class="literal">upper_inf('(,)'::daterange)</code></td><td><code class="literal">true</code></td></tr><tr><td>
<code class="literal">
<code class="function">range_merge</code>(<code class="type">anyrange</code>, <code class="type">anyrange</code>)
</code>
</td><td><code class="type">anyrange</code></td><td>the smallest range which includes both of the given ranges</td><td><code class="literal">range_merge('[1,2)'::int4range, '[3,4)'::int4range)</code></td><td><code class="literal">[1,4)</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
The <code class="function">lower</code> and <code class="function">upper</code> functions return null
if the range is empty or the requested bound is infinite.
The <code class="function">lower_inc</code>, <code class="function">upper_inc</code>,
<code class="function">lower_inf</code>, and <code class="function">upper_inf</code>
functions all return false for an empty range.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-array.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-aggregate.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.18. Array Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.20. Aggregate Functions</td></tr></table></div></body></html>
