codekingpro/portable-devtools
115k
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">@></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) @> 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">@></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 @> '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"><@</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) <@ 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"><@</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 <@ 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">&&</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) && 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"><<</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) << 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">>></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) >> 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">&<</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) &< 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">&></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) &> 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">@></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 @> '{[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">@></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 @> 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">@></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 @> '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">@></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 @> '{[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"><@</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 <@ '{[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"><@</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 <@ 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"><@</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) <@ '{[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"><@</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 <@ '{[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">&&</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 && '{[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">&&</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 && 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">&&</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) && '{[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"><<</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 << '{[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"><<</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 << 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"><<</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) << '{[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">>></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 >> '{[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">>></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 >> 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">>></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) >> '{[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">&<</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 &< '{[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">&<</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 &< 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">&<</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) &< '{[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">&></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 &> '{[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">&></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 &> 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">&></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) &> '{[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>