Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-range.html711 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.20. Range/Multirange 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.19. Array Functions and Operators" /><link rel="next" href="functions-aggregate.html" title="9.21. Aggregate Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.20. Range/Multirange Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-array.html" title="9.19. 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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-aggregate.html" title="9.21. Aggregate Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-RANGE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.20. Range/Multirange Functions and Operators <a href="#FUNCTIONS-RANGE" class="id_link">#</a></h2></div></div></div><p>3   See <a class="xref" href="rangetypes.html" title="8.17. Range Types">Section 8.17</a> for an overview of range types.4  </p><p>5   <a class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.55. Range Operators">Table 9.55</a> shows the specialized operators6   available for range types.7   <a class="xref" href="functions-range.html#MULTIRANGE-OPERATORS-TABLE" title="Table 9.56. Multirange Operators">Table 9.56</a> shows the specialized operators8   available for multirange types.9   In addition to those, the usual comparison operators shown in10   <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are available for range11   and multirange types.  The comparison operators order first by the range lower12   bounds, and only if those are equal do they compare the upper bounds.  The13   multirange operators compare each range until one is unequal. This14   does not usually result in a useful overall ordering, but the operators are15   provided to allow unique indexes to be constructed on ranges.16  </p><div class="table" id="RANGE-OPERATORS-TABLE"><p class="title"><strong>Table 9.55. Range Operators</strong></p><div class="table-contents"><table class="table" summary="Range Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">17        Operator18       </p>19       <p>20        Description21       </p>22       <p>23        Example(s)24       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">25        <code class="type">anyrange</code> <code class="literal">@&gt;</code> <code class="type">anyrange</code>26        → <code class="returnvalue">boolean</code>27       </p>28       <p>29        Does the first range contain the second?30       </p>31       <p>32        <code class="literal">int4range(2,4) @&gt; int4range(2,3)</code>33        → <code class="returnvalue">t</code>34       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">35        <code class="type">anyrange</code> <code class="literal">@&gt;</code> <code class="type">anyelement</code>36        → <code class="returnvalue">boolean</code>37       </p>38       <p>39        Does the range contain the element?40       </p>41       <p>42        <code class="literal">'[2011-01-01,2011-03-01)'::tsrange @&gt; '2011-01-10'::timestamp</code>43        → <code class="returnvalue">t</code>44       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">45        <code class="type">anyrange</code> <code class="literal">&lt;@</code> <code class="type">anyrange</code>46        → <code class="returnvalue">boolean</code>47       </p>48       <p>49        Is the first range contained by the second?50       </p>51       <p>52        <code class="literal">int4range(2,4) &lt;@ int4range(1,7)</code>53        → <code class="returnvalue">t</code>54       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">55        <code class="type">anyelement</code> <code class="literal">&lt;@</code> <code class="type">anyrange</code>56        → <code class="returnvalue">boolean</code>57       </p>58       <p>59        Is the element contained in the range?60       </p>61       <p>62        <code class="literal">42 &lt;@ int4range(1,7)</code>63        → <code class="returnvalue">f</code>64       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">65        <code class="type">anyrange</code> <code class="literal">&amp;&amp;</code> <code class="type">anyrange</code>66        → <code class="returnvalue">boolean</code>67       </p>68       <p>69        Do the ranges overlap, that is, have any elements in common?70       </p>71       <p>72        <code class="literal">int8range(3,7) &amp;&amp; int8range(4,12)</code>73        → <code class="returnvalue">t</code>74       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">75        <code class="type">anyrange</code> <code class="literal">&lt;&lt;</code> <code class="type">anyrange</code>76        → <code class="returnvalue">boolean</code>77       </p>78       <p>79        Is the first range strictly left of the second?80       </p>81       <p>82        <code class="literal">int8range(1,10) &lt;&lt; int8range(100,110)</code>83        → <code class="returnvalue">t</code>84       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">85        <code class="type">anyrange</code> <code class="literal">&gt;&gt;</code> <code class="type">anyrange</code>86        → <code class="returnvalue">boolean</code>87       </p>88       <p>89        Is the first range strictly right of the second?90       </p>91       <p>92        <code class="literal">int8range(50,60) &gt;&gt; int8range(20,30)</code>93        → <code class="returnvalue">t</code>94       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">95        <code class="type">anyrange</code> <code class="literal">&amp;&lt;</code> <code class="type">anyrange</code>96        → <code class="returnvalue">boolean</code>97       </p>98       <p>99        Does the first range not extend to the right of the second?100       </p>101       <p>102        <code class="literal">int8range(1,20) &amp;&lt; int8range(18,20)</code>103        → <code class="returnvalue">t</code>104       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">105        <code class="type">anyrange</code> <code class="literal">&amp;&gt;</code> <code class="type">anyrange</code>106        → <code class="returnvalue">boolean</code>107       </p>108       <p>109        Does the first range not extend to the left of the second?110       </p>111       <p>112        <code class="literal">int8range(7,20) &amp;&gt; int8range(5,10)</code>113        → <code class="returnvalue">t</code>114       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">115        <code class="type">anyrange</code> <code class="literal">-|-</code> <code class="type">anyrange</code>116        → <code class="returnvalue">boolean</code>117       </p>118       <p>119        Are the ranges adjacent?120       </p>121       <p>122        <code class="literal">numrange(1.1,2.2) -|- numrange(2.2,3.3)</code>123        → <code class="returnvalue">t</code>124       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">125        <code class="type">anyrange</code> <code class="literal">+</code> <code class="type">anyrange</code>126        → <code class="returnvalue">anyrange</code>127       </p>128       <p>129        Computes the union of the ranges.  The ranges must overlap or be130        adjacent, so that the union is a single range (but131        see <code class="function">range_merge()</code>).132       </p>133       <p>134        <code class="literal">numrange(5,15) + numrange(10,20)</code>135        → <code class="returnvalue">[5,20)</code>136       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">137        <code class="type">anyrange</code> <code class="literal">*</code> <code class="type">anyrange</code>138        → <code class="returnvalue">anyrange</code>139       </p>140       <p>141        Computes the intersection of the ranges.142       </p>143       <p>144        <code class="literal">int8range(5,15) * int8range(10,20)</code>145        → <code class="returnvalue">[10,15)</code>146       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">147        <code class="type">anyrange</code> <code class="literal">-</code> <code class="type">anyrange</code>148        → <code class="returnvalue">anyrange</code>149       </p>150       <p>151        Computes the difference of the ranges.  The second range must not be152        contained in the first in such a way that the difference would not be153        a single range.154       </p>155       <p>156        <code class="literal">int8range(5,15) - int8range(10,20)</code>157        → <code class="returnvalue">[5,10)</code>158       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="MULTIRANGE-OPERATORS-TABLE"><p class="title"><strong>Table 9.56. Multirange Operators</strong></p><div class="table-contents"><table class="table" summary="Multirange Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">159        Operator160       </p>161       <p>162        Description163       </p>164       <p>165        Example(s)166       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">167        <code class="type">anymultirange</code> <code class="literal">@&gt;</code> <code class="type">anymultirange</code>168        → <code class="returnvalue">boolean</code>169       </p>170       <p>171        Does the first multirange contain the second?172       </p>173       <p>174        <code class="literal">'{[2,4)}'::int4multirange @&gt; '{[2,3)}'::int4multirange</code>175        → <code class="returnvalue">t</code>176       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">177        <code class="type">anymultirange</code> <code class="literal">@&gt;</code> <code class="type">anyrange</code>178        → <code class="returnvalue">boolean</code>179       </p>180       <p>181        Does the multirange contain the range?182       </p>183       <p>184        <code class="literal">'{[2,4)}'::int4multirange @&gt; int4range(2,3)</code>185        → <code class="returnvalue">t</code>186       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">187        <code class="type">anymultirange</code> <code class="literal">@&gt;</code> <code class="type">anyelement</code>188        → <code class="returnvalue">boolean</code>189       </p>190       <p>191        Does the multirange contain the element?192       </p>193       <p>194        <code class="literal">'{[2011-01-01,2011-03-01)}'::tsmultirange @&gt; '2011-01-10'::timestamp</code>195        → <code class="returnvalue">t</code>196       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">197        <code class="type">anyrange</code> <code class="literal">@&gt;</code> <code class="type">anymultirange</code>198        → <code class="returnvalue">boolean</code>199       </p>200       <p>201        Does the range contain the multirange?202       </p>203       <p>204        <code class="literal">'[2,4)'::int4range @&gt; '{[2,3)}'::int4multirange</code>205        → <code class="returnvalue">t</code>206       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">207        <code class="type">anymultirange</code> <code class="literal">&lt;@</code> <code class="type">anymultirange</code>208        → <code class="returnvalue">boolean</code>209       </p>210       <p>211        Is the first multirange contained by the second?212       </p>213       <p>214        <code class="literal">'{[2,4)}'::int4multirange &lt;@ '{[1,7)}'::int4multirange</code>215        → <code class="returnvalue">t</code>216       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">217        <code class="type">anymultirange</code> <code class="literal">&lt;@</code> <code class="type">anyrange</code>218        → <code class="returnvalue">boolean</code>219       </p>220       <p>221        Is the multirange contained by the range?222       </p>223       <p>224        <code class="literal">'{[2,4)}'::int4multirange &lt;@ int4range(1,7)</code>225        → <code class="returnvalue">t</code>226       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">227        <code class="type">anyrange</code> <code class="literal">&lt;@</code> <code class="type">anymultirange</code>228        → <code class="returnvalue">boolean</code>229       </p>230       <p>231        Is the range contained by the multirange?232       </p>233       <p>234        <code class="literal">int4range(2,4) &lt;@ '{[1,7)}'::int4multirange</code>235        → <code class="returnvalue">t</code>236       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">237        <code class="type">anyelement</code> <code class="literal">&lt;@</code> <code class="type">anymultirange</code>238        → <code class="returnvalue">boolean</code>239       </p>240       <p>241        Is the element contained by the multirange?242       </p>243       <p>244        <code class="literal">4 &lt;@ '{[1,7)}'::int4multirange</code>245        → <code class="returnvalue">t</code>246       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">247        <code class="type">anymultirange</code> <code class="literal">&amp;&amp;</code> <code class="type">anymultirange</code>248        → <code class="returnvalue">boolean</code>249       </p>250       <p>251        Do the multiranges overlap, that is, have any elements in common?252       </p>253       <p>254        <code class="literal">'{[3,7)}'::int8multirange &amp;&amp; '{[4,12)}'::int8multirange</code>255        → <code class="returnvalue">t</code>256       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">257        <code class="type">anymultirange</code> <code class="literal">&amp;&amp;</code> <code class="type">anyrange</code>258        → <code class="returnvalue">boolean</code>259       </p>260       <p>261        Does the multirange overlap the range?262       </p>263       <p>264        <code class="literal">'{[3,7)}'::int8multirange &amp;&amp; int8range(4,12)</code>265        → <code class="returnvalue">t</code>266       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">267        <code class="type">anyrange</code> <code class="literal">&amp;&amp;</code> <code class="type">anymultirange</code>268        → <code class="returnvalue">boolean</code>269       </p>270       <p>271        Does the range overlap the multirange?272       </p>273       <p>274        <code class="literal">int8range(3,7) &amp;&amp; '{[4,12)}'::int8multirange</code>275        → <code class="returnvalue">t</code>276       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">277        <code class="type">anymultirange</code> <code class="literal">&lt;&lt;</code> <code class="type">anymultirange</code>278        → <code class="returnvalue">boolean</code>279       </p>280       <p>281        Is the first multirange strictly left of the second?282       </p>283       <p>284        <code class="literal">'{[1,10)}'::int8multirange &lt;&lt; '{[100,110)}'::int8multirange</code>285        → <code class="returnvalue">t</code>286       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">287        <code class="type">anymultirange</code> <code class="literal">&lt;&lt;</code> <code class="type">anyrange</code>288        → <code class="returnvalue">boolean</code>289       </p>290       <p>291        Is the multirange strictly left of the range?292       </p>293       <p>294        <code class="literal">'{[1,10)}'::int8multirange &lt;&lt; int8range(100,110)</code>295        → <code class="returnvalue">t</code>296       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">297        <code class="type">anyrange</code> <code class="literal">&lt;&lt;</code> <code class="type">anymultirange</code>298        → <code class="returnvalue">boolean</code>299       </p>300       <p>301        Is the range strictly left of the multirange?302       </p>303       <p>304        <code class="literal">int8range(1,10) &lt;&lt; '{[100,110)}'::int8multirange</code>305        → <code class="returnvalue">t</code>306       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">307        <code class="type">anymultirange</code> <code class="literal">&gt;&gt;</code> <code class="type">anymultirange</code>308        → <code class="returnvalue">boolean</code>309       </p>310       <p>311        Is the first multirange strictly right of the second?312       </p>313       <p>314        <code class="literal">'{[50,60)}'::int8multirange &gt;&gt; '{[20,30)}'::int8multirange</code>315        → <code class="returnvalue">t</code>316       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">317        <code class="type">anymultirange</code> <code class="literal">&gt;&gt;</code> <code class="type">anyrange</code>318        → <code class="returnvalue">boolean</code>319       </p>320       <p>321        Is the multirange strictly right of the range?322       </p>323       <p>324        <code class="literal">'{[50,60)}'::int8multirange &gt;&gt; int8range(20,30)</code>325        → <code class="returnvalue">t</code>326       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">327        <code class="type">anyrange</code> <code class="literal">&gt;&gt;</code> <code class="type">anymultirange</code>328        → <code class="returnvalue">boolean</code>329       </p>330       <p>331        Is the range strictly right of the multirange?332       </p>333       <p>334        <code class="literal">int8range(50,60) &gt;&gt; '{[20,30)}'::int8multirange</code>335        → <code class="returnvalue">t</code>336       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">337        <code class="type">anymultirange</code> <code class="literal">&amp;&lt;</code> <code class="type">anymultirange</code>338        → <code class="returnvalue">boolean</code>339       </p>340       <p>341        Does the first multirange not extend to the right of the second?342       </p>343       <p>344        <code class="literal">'{[1,20)}'::int8multirange &amp;&lt; '{[18,20)}'::int8multirange</code>345        → <code class="returnvalue">t</code>346       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">347        <code class="type">anymultirange</code> <code class="literal">&amp;&lt;</code> <code class="type">anyrange</code>348        → <code class="returnvalue">boolean</code>349       </p>350       <p>351        Does the multirange not extend to the right of the range?352       </p>353       <p>354        <code class="literal">'{[1,20)}'::int8multirange &amp;&lt; int8range(18,20)</code>355        → <code class="returnvalue">t</code>356       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">357        <code class="type">anyrange</code> <code class="literal">&amp;&lt;</code> <code class="type">anymultirange</code>358        → <code class="returnvalue">boolean</code>359       </p>360       <p>361        Does the range not extend to the right of the multirange?362       </p>363       <p>364        <code class="literal">int8range(1,20) &amp;&lt; '{[18,20)}'::int8multirange</code>365        → <code class="returnvalue">t</code>366       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">367        <code class="type">anymultirange</code> <code class="literal">&amp;&gt;</code> <code class="type">anymultirange</code>368        → <code class="returnvalue">boolean</code>369       </p>370       <p>371        Does the first multirange not extend to the left of the second?372       </p>373       <p>374        <code class="literal">'{[7,20)}'::int8multirange &amp;&gt; '{[5,10)}'::int8multirange</code>375        → <code class="returnvalue">t</code>376       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">377        <code class="type">anymultirange</code> <code class="literal">&amp;&gt;</code> <code class="type">anyrange</code>378        → <code class="returnvalue">boolean</code>379       </p>380       <p>381        Does the multirange not extend to the left of the range?382       </p>383       <p>384        <code class="literal">'{[7,20)}'::int8multirange &amp;&gt; int8range(5,10)</code>385        → <code class="returnvalue">t</code>386       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">387        <code class="type">anyrange</code> <code class="literal">&amp;&gt;</code> <code class="type">anymultirange</code>388        → <code class="returnvalue">boolean</code>389       </p>390       <p>391        Does the range not extend to the left of the multirange?392       </p>393       <p>394        <code class="literal">int8range(7,20) &amp;&gt; '{[5,10)}'::int8multirange</code>395        → <code class="returnvalue">t</code>396       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">397        <code class="type">anymultirange</code> <code class="literal">-|-</code> <code class="type">anymultirange</code>398        → <code class="returnvalue">boolean</code>399       </p>400       <p>401        Are the multiranges adjacent?402       </p>403       <p>404        <code class="literal">'{[1.1,2.2)}'::nummultirange -|- '{[2.2,3.3)}'::nummultirange</code>405        → <code class="returnvalue">t</code>406       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">407        <code class="type">anymultirange</code> <code class="literal">-|-</code> <code class="type">anyrange</code>408        → <code class="returnvalue">boolean</code>409       </p>410       <p>411        Is the multirange adjacent to the range?412       </p>413       <p>414        <code class="literal">'{[1.1,2.2)}'::nummultirange -|- numrange(2.2,3.3)</code>415        → <code class="returnvalue">t</code>416       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">417        <code class="type">anyrange</code> <code class="literal">-|-</code> <code class="type">anymultirange</code>418        → <code class="returnvalue">boolean</code>419       </p>420       <p>421        Is the range adjacent to the multirange?422       </p>423       <p>424        <code class="literal">numrange(1.1,2.2) -|- '{[2.2,3.3)}'::nummultirange</code>425        → <code class="returnvalue">t</code>426       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">427        <code class="type">anymultirange</code> <code class="literal">+</code> <code class="type">anymultirange</code>428        → <code class="returnvalue">anymultirange</code>429       </p>430       <p>431        Computes the union of the multiranges.  The multiranges need not overlap432        or be adjacent.433       </p>434       <p>435        <code class="literal">'{[5,10)}'::nummultirange + '{[15,20)}'::nummultirange</code>436        → <code class="returnvalue">{[5,10), [15,20)}</code>437       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">438        <code class="type">anymultirange</code> <code class="literal">*</code> <code class="type">anymultirange</code>439        → <code class="returnvalue">anymultirange</code>440       </p>441       <p>442        Computes the intersection of the multiranges.443       </p>444       <p>445        <code class="literal">'{[5,15)}'::int8multirange * '{[10,20)}'::int8multirange</code>446        → <code class="returnvalue">{[10,15)}</code>447       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">448        <code class="type">anymultirange</code> <code class="literal">-</code> <code class="type">anymultirange</code>449        → <code class="returnvalue">anymultirange</code>450       </p>451       <p>452        Computes the difference of the multiranges.453       </p>454       <p>455        <code class="literal">'{[5,20)}'::int8multirange - '{[10,15)}'::int8multirange</code>456        → <code class="returnvalue">{[5,10), [15,20)}</code>457       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>458   The left-of/right-of/adjacent operators always return false when an empty459   range or multirange is involved; that is, an empty range is not considered to460   be either before or after any other range.461  </p><p>462   Elsewhere empty ranges and multiranges are treated as the additive identity:463   anything unioned with an empty value is itself. Anything minus an empty464   value is itself. An empty multirange has exactly the same points as an empty465   range. Every range contains the empty range. Every multirange contains as many466   empty ranges as you like.467  </p><p>468   The range union and difference operators will fail if the resulting range would469   need to contain two disjoint sub-ranges, as such a range cannot be470   represented. There are separate operators for union and difference that take471   multirange parameters and return a multirange, and they do not fail even if472   their arguments are disjoint. So if you need a union or difference operation473   for ranges that may be disjoint, you can avoid errors by first casting your474   ranges to multiranges.475  </p><p>476   <a class="xref" href="functions-range.html#RANGE-FUNCTIONS-TABLE" title="Table 9.57. Range Functions">Table 9.57</a> shows the functions477   available for use with range types.478   <a class="xref" href="functions-range.html#MULTIRANGE-FUNCTIONS-TABLE" title="Table 9.58. Multirange Functions">Table 9.58</a> shows the functions479   available for use with multirange types.480  </p><div class="table" id="RANGE-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.57. Range Functions</strong></p><div class="table-contents"><table class="table" summary="Range Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">481        Function482       </p>483       <p>484        Description485       </p>486       <p>487        Example(s)488       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">489        <a id="id-1.5.8.26.10.2.2.1.1.1.1" class="indexterm"></a>490        <code class="function">lower</code> ( <code class="type">anyrange</code> )491        → <code class="returnvalue">anyelement</code>492       </p>493       <p>494        Extracts the lower bound of the range (<code class="literal">NULL</code> if the495        range is empty or has no lower bound).496       </p>497       <p>498        <code class="literal">lower(numrange(1.1,2.2))</code>499        → <code class="returnvalue">1.1</code>500       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">501        <a id="id-1.5.8.26.10.2.2.2.1.1.1" class="indexterm"></a>502        <code class="function">upper</code> ( <code class="type">anyrange</code> )503        → <code class="returnvalue">anyelement</code>504       </p>505       <p>506        Extracts the upper bound of the range (<code class="literal">NULL</code> if the507        range is empty or has no upper bound).508       </p>509       <p>510        <code class="literal">upper(numrange(1.1,2.2))</code>511        → <code class="returnvalue">2.2</code>512       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">513        <a id="id-1.5.8.26.10.2.2.3.1.1.1" class="indexterm"></a>514        <code class="function">isempty</code> ( <code class="type">anyrange</code> )515        → <code class="returnvalue">boolean</code>516       </p>517       <p>518        Is the range empty?519       </p>520       <p>521        <code class="literal">isempty(numrange(1.1,2.2))</code>522        → <code class="returnvalue">f</code>523       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">524        <a id="id-1.5.8.26.10.2.2.4.1.1.1" class="indexterm"></a>525        <code class="function">lower_inc</code> ( <code class="type">anyrange</code> )526        → <code class="returnvalue">boolean</code>527       </p>528       <p>529        Is the range's lower bound inclusive?530       </p>531       <p>532        <code class="literal">lower_inc(numrange(1.1,2.2))</code>533        → <code class="returnvalue">t</code>534       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">535        <a id="id-1.5.8.26.10.2.2.5.1.1.1" class="indexterm"></a>536        <code class="function">upper_inc</code> ( <code class="type">anyrange</code> )537        → <code class="returnvalue">boolean</code>538       </p>539       <p>540        Is the range's upper bound inclusive?541       </p>542       <p>543        <code class="literal">upper_inc(numrange(1.1,2.2))</code>544        → <code class="returnvalue">f</code>545       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">546        <a id="id-1.5.8.26.10.2.2.6.1.1.1" class="indexterm"></a>547        <code class="function">lower_inf</code> ( <code class="type">anyrange</code> )548        → <code class="returnvalue">boolean</code>549       </p>550       <p>551        Does the range have no lower bound?  (A lower bound of552        <code class="literal">-Infinity</code> returns false.)553       </p>554       <p>555        <code class="literal">lower_inf('(,)'::daterange)</code>556        → <code class="returnvalue">t</code>557       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">558        <a id="id-1.5.8.26.10.2.2.7.1.1.1" class="indexterm"></a>559        <code class="function">upper_inf</code> ( <code class="type">anyrange</code> )560        → <code class="returnvalue">boolean</code>561       </p>562       <p>563        Does the range have no upper bound?  (An upper bound of564        <code class="literal">Infinity</code> returns false.)565       </p>566       <p>567        <code class="literal">upper_inf('(,)'::daterange)</code>568        → <code class="returnvalue">t</code>569       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">570        <a id="id-1.5.8.26.10.2.2.8.1.1.1" class="indexterm"></a>571        <code class="function">range_merge</code> ( <code class="type">anyrange</code>, <code class="type">anyrange</code> )572        → <code class="returnvalue">anyrange</code>573       </p>574       <p>575        Computes the smallest range that includes both of the given ranges.576       </p>577       <p>578        <code class="literal">range_merge('[1,2)'::int4range, '[3,4)'::int4range)</code>579        → <code class="returnvalue">[1,4)</code>580       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="MULTIRANGE-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.58. Multirange Functions</strong></p><div class="table-contents"><table class="table" summary="Multirange Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">581        Function582       </p>583       <p>584        Description585       </p>586       <p>587        Example(s)588       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">589        <a id="id-1.5.8.26.11.2.2.1.1.1.1" class="indexterm"></a>590        <code class="function">lower</code> ( <code class="type">anymultirange</code> )591        → <code class="returnvalue">anyelement</code>592       </p>593       <p>594        Extracts the lower bound of the multirange (<code class="literal">NULL</code> if the595        multirange is empty has no lower bound).596       </p>597       <p>598        <code class="literal">lower('{[1.1,2.2)}'::nummultirange)</code>599        → <code class="returnvalue">1.1</code>600       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">601        <a id="id-1.5.8.26.11.2.2.2.1.1.1" class="indexterm"></a>602        <code class="function">upper</code> ( <code class="type">anymultirange</code> )603        → <code class="returnvalue">anyelement</code>604       </p>605       <p>606        Extracts the upper bound of the multirange (<code class="literal">NULL</code> if the607        multirange is empty or has no upper bound).608       </p>609       <p>610        <code class="literal">upper('{[1.1,2.2)}'::nummultirange)</code>611        → <code class="returnvalue">2.2</code>612       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">613        <a id="id-1.5.8.26.11.2.2.3.1.1.1" class="indexterm"></a>614        <code class="function">isempty</code> ( <code class="type">anymultirange</code> )615        → <code class="returnvalue">boolean</code>616       </p>617       <p>618        Is the multirange empty?619       </p>620       <p>621        <code class="literal">isempty('{[1.1,2.2)}'::nummultirange)</code>622        → <code class="returnvalue">f</code>623       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">624        <a id="id-1.5.8.26.11.2.2.4.1.1.1" class="indexterm"></a>625        <code class="function">lower_inc</code> ( <code class="type">anymultirange</code> )626        → <code class="returnvalue">boolean</code>627       </p>628       <p>629        Is the multirange's lower bound inclusive?630       </p>631       <p>632        <code class="literal">lower_inc('{[1.1,2.2)}'::nummultirange)</code>633        → <code class="returnvalue">t</code>634       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">635        <a id="id-1.5.8.26.11.2.2.5.1.1.1" class="indexterm"></a>636        <code class="function">upper_inc</code> ( <code class="type">anymultirange</code> )637        → <code class="returnvalue">boolean</code>638       </p>639       <p>640        Is the multirange's upper bound inclusive?641       </p>642       <p>643        <code class="literal">upper_inc('{[1.1,2.2)}'::nummultirange)</code>644        → <code class="returnvalue">f</code>645       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">646        <a id="id-1.5.8.26.11.2.2.6.1.1.1" class="indexterm"></a>647        <code class="function">lower_inf</code> ( <code class="type">anymultirange</code> )648        → <code class="returnvalue">boolean</code>649       </p>650       <p>651        Does the multirange have no lower bound?  (A lower bound of652        <code class="literal">-Infinity</code> returns false.)653       </p>654       <p>655        <code class="literal">lower_inf('{(,)}'::datemultirange)</code>656        → <code class="returnvalue">t</code>657       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">658        <a id="id-1.5.8.26.11.2.2.7.1.1.1" class="indexterm"></a>659        <code class="function">upper_inf</code> ( <code class="type">anymultirange</code> )660        → <code class="returnvalue">boolean</code>661       </p>662       <p>663        Does the multirange have no upper bound?  (An upper bound of664        <code class="literal">Infinity</code> returns false.)665       </p>666       <p>667        <code class="literal">upper_inf('{(,)}'::datemultirange)</code>668        → <code class="returnvalue">t</code>669       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">670        <a id="id-1.5.8.26.11.2.2.8.1.1.1" class="indexterm"></a>671        <code class="function">range_merge</code> ( <code class="type">anymultirange</code> )672        → <code class="returnvalue">anyrange</code>673       </p>674       <p>675        Computes the smallest range that includes the entire multirange.676       </p>677       <p>678        <code class="literal">range_merge('{[1,2), [3,4)}'::int4multirange)</code>679        → <code class="returnvalue">[1,4)</code>680       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">681        <a id="id-1.5.8.26.11.2.2.9.1.1.1" class="indexterm"></a>682        <code class="function">multirange</code> ( <code class="type">anyrange</code> )683        → <code class="returnvalue">anymultirange</code>684       </p>685       <p>686        Returns a multirange containing just the given range.687       </p>688       <p>689        <code class="literal">multirange('[1,2)'::int4range)</code>690        → <code class="returnvalue">{[1,2)}</code>691       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">692        <a id="id-1.5.8.26.11.2.2.10.1.1.1" class="indexterm"></a>693        <code class="function">unnest</code> ( <code class="type">anymultirange</code> )694        → <code class="returnvalue">setof anyrange</code>695       </p>696       <p>697        Expands a multirange into a set of ranges.698        The ranges are read out in storage order (ascending).699       </p>700       <p>701        <code class="literal">unnest('{[1,2), [3,4)}'::int4multirange)</code>702        → <code class="returnvalue"></code>703</p><pre class="programlisting">704 [1,2)705 [3,4)706</pre><p>707       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>708   The <code class="function">lower_inc</code>, <code class="function">upper_inc</code>,709   <code class="function">lower_inf</code>, and <code class="function">upper_inf</code>710   functions all return false for an empty range or multirange.711  </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" title="9.19. Array Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-aggregate.html" title="9.21. Aggregate Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.19. Array Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.21. Aggregate Functions</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai