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.2. Comparison 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-logical.html" title="9.1. Logical Operators" /><link rel="next" href="functions-math.html" title="9.3. Mathematical Functions and Operators" /></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.2. Comparison Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-logical.html" title="9.1. Logical 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-math.html" title="9.3. Mathematical Functions and Operators">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-COMPARISON"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.2. Comparison Functions and Operators <a href="#FUNCTIONS-COMPARISON" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.8.2" class="indexterm"></a><p>3 The usual comparison operators are available, as shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a>.4 </p><div class="table" id="FUNCTIONS-COMPARISON-OP-TABLE"><p class="title"><strong>Table 9.1. Comparison Operators</strong></p><div class="table-contents"><table class="table" summary="Comparison Operators" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th></tr></thead><tbody><tr><td>5 <em class="replaceable"><code>datatype</code></em> <code class="literal"><</code> <em class="replaceable"><code>datatype</code></em>6 → <code class="returnvalue">boolean</code>7 </td><td>Less than</td></tr><tr><td>8 <em class="replaceable"><code>datatype</code></em> <code class="literal">></code> <em class="replaceable"><code>datatype</code></em>9 → <code class="returnvalue">boolean</code>10 </td><td>Greater than</td></tr><tr><td>11 <em class="replaceable"><code>datatype</code></em> <code class="literal"><=</code> <em class="replaceable"><code>datatype</code></em>12 → <code class="returnvalue">boolean</code>13 </td><td>Less than or equal to</td></tr><tr><td>14 <em class="replaceable"><code>datatype</code></em> <code class="literal">>=</code> <em class="replaceable"><code>datatype</code></em>15 → <code class="returnvalue">boolean</code>16 </td><td>Greater than or equal to</td></tr><tr><td>17 <em class="replaceable"><code>datatype</code></em> <code class="literal">=</code> <em class="replaceable"><code>datatype</code></em>18 → <code class="returnvalue">boolean</code>19 </td><td>Equal</td></tr><tr><td>20 <em class="replaceable"><code>datatype</code></em> <code class="literal"><></code> <em class="replaceable"><code>datatype</code></em>21 → <code class="returnvalue">boolean</code>22 </td><td>Not equal</td></tr><tr><td>23 <em class="replaceable"><code>datatype</code></em> <code class="literal">!=</code> <em class="replaceable"><code>datatype</code></em>24 → <code class="returnvalue">boolean</code>25 </td><td>Not equal</td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>26 <code class="literal"><></code> is the standard SQL notation for <span class="quote">“<span class="quote">not27 equal</span>”</span>. <code class="literal">!=</code> is an alias, which is converted28 to <code class="literal"><></code> at a very early stage of parsing.29 Hence, it is not possible to implement <code class="literal">!=</code>30 and <code class="literal"><></code> operators that do different things.31 </p></div><p>32 These comparison operators are available for all built-in data types33 that have a natural ordering, including numeric, string, and date/time34 types. In addition, arrays, composite types, and ranges can be compared35 if their component data types are comparable.36 </p><p>37 It is usually possible to compare values of related data38 types as well; for example <code class="type">integer</code> <code class="literal">></code>39 <code class="type">bigint</code> will work. Some cases of this sort are implemented40 directly by <span class="quote">“<span class="quote">cross-type</span>”</span> comparison operators, but if no41 such operator is available, the parser will coerce the less-general type42 to the more-general type and apply the latter's comparison operator.43 </p><p>44 As shown above, all comparison operators are binary operators that45 return values of type <code class="type">boolean</code>. Thus, expressions like46 <code class="literal">1 < 2 < 3</code> are not valid (because there is47 no <code class="literal"><</code> operator to compare a Boolean value with48 <code class="literal">3</code>). Use the <code class="literal">BETWEEN</code> predicates49 shown below to perform range tests.50 </p><p>51 There are also some comparison predicates, as shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE" title="Table 9.2. Comparison Predicates">Table 9.2</a>. These behave much like52 operators, but have special syntax mandated by the SQL standard.53 </p><div class="table" id="FUNCTIONS-COMPARISON-PRED-TABLE"><p class="title"><strong>Table 9.2. Comparison Predicates</strong></p><div class="table-contents"><table class="table" summary="Comparison Predicates" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">54 Predicate55 </p>56 <p>57 Description58 </p>59 <p>60 Example(s)61 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">62 <em class="replaceable"><code>datatype</code></em> <code class="literal">BETWEEN</code> <em class="replaceable"><code>datatype</code></em> <code class="literal">AND</code> <em class="replaceable"><code>datatype</code></em>63 → <code class="returnvalue">boolean</code>64 </p>65 <p>66 Between (inclusive of the range endpoints).67 </p>68 <p>69 <code class="literal">2 BETWEEN 1 AND 3</code>70 → <code class="returnvalue">t</code>71 </p>72 <p>73 <code class="literal">2 BETWEEN 3 AND 1</code>74 → <code class="returnvalue">f</code>75 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">76 <em class="replaceable"><code>datatype</code></em> <code class="literal">NOT BETWEEN</code> <em class="replaceable"><code>datatype</code></em> <code class="literal">AND</code> <em class="replaceable"><code>datatype</code></em>77 → <code class="returnvalue">boolean</code>78 </p>79 <p>80 Not between (the negation of <code class="literal">BETWEEN</code>).81 </p>82 <p>83 <code class="literal">2 NOT BETWEEN 1 AND 3</code>84 → <code class="returnvalue">f</code>85 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">86 <em class="replaceable"><code>datatype</code></em> <code class="literal">BETWEEN SYMMETRIC</code> <em class="replaceable"><code>datatype</code></em> <code class="literal">AND</code> <em class="replaceable"><code>datatype</code></em>87 → <code class="returnvalue">boolean</code>88 </p>89 <p>90 Between, after sorting the two endpoint values.91 </p>92 <p>93 <code class="literal">2 BETWEEN SYMMETRIC 3 AND 1</code>94 → <code class="returnvalue">t</code>95 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">96 <em class="replaceable"><code>datatype</code></em> <code class="literal">NOT BETWEEN SYMMETRIC</code> <em class="replaceable"><code>datatype</code></em> <code class="literal">AND</code> <em class="replaceable"><code>datatype</code></em>97 → <code class="returnvalue">boolean</code>98 </p>99 <p>100 Not between, after sorting the two endpoint values.101 </p>102 <p>103 <code class="literal">2 NOT BETWEEN SYMMETRIC 3 AND 1</code>104 → <code class="returnvalue">f</code>105 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">106 <em class="replaceable"><code>datatype</code></em> <code class="literal">IS DISTINCT FROM</code> <em class="replaceable"><code>datatype</code></em>107 → <code class="returnvalue">boolean</code>108 </p>109 <p>110 Not equal, treating null as a comparable value.111 </p>112 <p>113 <code class="literal">1 IS DISTINCT FROM NULL</code>114 → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>)115 </p>116 <p>117 <code class="literal">NULL IS DISTINCT FROM NULL</code>118 → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>)119 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">120 <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NOT DISTINCT FROM</code> <em class="replaceable"><code>datatype</code></em>121 → <code class="returnvalue">boolean</code>122 </p>123 <p>124 Equal, treating null as a comparable value.125 </p>126 <p>127 <code class="literal">1 IS NOT DISTINCT FROM NULL</code>128 → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>)129 </p>130 <p>131 <code class="literal">NULL IS NOT DISTINCT FROM NULL</code>132 → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>)133 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">134 <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NULL</code>135 → <code class="returnvalue">boolean</code>136 </p>137 <p>138 Test whether value is null.139 </p>140 <p>141 <code class="literal">1.5 IS NULL</code>142 → <code class="returnvalue">f</code>143 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">144 <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NOT NULL</code>145 → <code class="returnvalue">boolean</code>146 </p>147 <p>148 Test whether value is not null.149 </p>150 <p>151 <code class="literal">'null' IS NOT NULL</code>152 → <code class="returnvalue">t</code>153 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">154 <em class="replaceable"><code>datatype</code></em> <code class="literal">ISNULL</code>155 → <code class="returnvalue">boolean</code>156 </p>157 <p>158 Test whether value is null (nonstandard syntax).159 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">160 <em class="replaceable"><code>datatype</code></em> <code class="literal">NOTNULL</code>161 → <code class="returnvalue">boolean</code>162 </p>163 <p>164 Test whether value is not null (nonstandard syntax).165 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">166 <code class="type">boolean</code> <code class="literal">IS TRUE</code>167 → <code class="returnvalue">boolean</code>168 </p>169 <p>170 Test whether boolean expression yields true.171 </p>172 <p>173 <code class="literal">true IS TRUE</code>174 → <code class="returnvalue">t</code>175 </p>176 <p>177 <code class="literal">NULL::boolean IS TRUE</code>178 → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>)179 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">180 <code class="type">boolean</code> <code class="literal">IS NOT TRUE</code>181 → <code class="returnvalue">boolean</code>182 </p>183 <p>184 Test whether boolean expression yields false or unknown.185 </p>186 <p>187 <code class="literal">true IS NOT TRUE</code>188 → <code class="returnvalue">f</code>189 </p>190 <p>191 <code class="literal">NULL::boolean IS NOT TRUE</code>192 → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>)193 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">194 <code class="type">boolean</code> <code class="literal">IS FALSE</code>195 → <code class="returnvalue">boolean</code>196 </p>197 <p>198 Test whether boolean expression yields false.199 </p>200 <p>201 <code class="literal">true IS FALSE</code>202 → <code class="returnvalue">f</code>203 </p>204 <p>205 <code class="literal">NULL::boolean IS FALSE</code>206 → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>)207 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">208 <code class="type">boolean</code> <code class="literal">IS NOT FALSE</code>209 → <code class="returnvalue">boolean</code>210 </p>211 <p>212 Test whether boolean expression yields true or unknown.213 </p>214 <p>215 <code class="literal">true IS NOT FALSE</code>216 → <code class="returnvalue">t</code>217 </p>218 <p>219 <code class="literal">NULL::boolean IS NOT FALSE</code>220 → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>)221 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">222 <code class="type">boolean</code> <code class="literal">IS UNKNOWN</code>223 → <code class="returnvalue">boolean</code>224 </p>225 <p>226 Test whether boolean expression yields unknown.227 </p>228 <p>229 <code class="literal">true IS UNKNOWN</code>230 → <code class="returnvalue">f</code>231 </p>232 <p>233 <code class="literal">NULL::boolean IS UNKNOWN</code>234 → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>)235 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">236 <code class="type">boolean</code> <code class="literal">IS NOT UNKNOWN</code>237 → <code class="returnvalue">boolean</code>238 </p>239 <p>240 Test whether boolean expression yields true or false.241 </p>242 <p>243 <code class="literal">true IS NOT UNKNOWN</code>244 → <code class="returnvalue">t</code>245 </p>246 <p>247 <code class="literal">NULL::boolean IS NOT UNKNOWN</code>248 → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>)249 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>250 <a id="id-1.5.8.8.11.1" class="indexterm"></a>251 <a id="id-1.5.8.8.11.2" class="indexterm"></a>252 The <code class="token">BETWEEN</code> predicate simplifies range tests:253</p><pre class="synopsis">254<em class="replaceable"><code>a</code></em> BETWEEN <em class="replaceable"><code>x</code></em> AND <em class="replaceable"><code>y</code></em>255</pre><p>256 is equivalent to257</p><pre class="synopsis">258<em class="replaceable"><code>a</code></em> >= <em class="replaceable"><code>x</code></em> AND <em class="replaceable"><code>a</code></em> <= <em class="replaceable"><code>y</code></em>259</pre><p>260 Notice that <code class="token">BETWEEN</code> treats the endpoint values as included261 in the range.262 <code class="literal">BETWEEN SYMMETRIC</code> is like <code class="literal">BETWEEN</code>263 except there is no requirement that the argument to the left of264 <code class="literal">AND</code> be less than or equal to the argument on the right.265 If it is not, those two arguments are automatically swapped, so that266 a nonempty range is always implied.267 </p><p>268 The various variants of <code class="literal">BETWEEN</code> are implemented in269 terms of the ordinary comparison operators, and therefore will work for270 any data type(s) that can be compared.271 </p><div class="note"><h3 class="title">Note</h3><p>272 The use of <code class="literal">AND</code> in the <code class="literal">BETWEEN</code>273 syntax creates an ambiguity with the use of <code class="literal">AND</code> as a274 logical operator. To resolve this, only a limited set of expression275 types are allowed as the second argument of a <code class="literal">BETWEEN</code>276 clause. If you need to write a more complex sub-expression277 in <code class="literal">BETWEEN</code>, write parentheses around the278 sub-expression.279 </p></div><p>280 <a id="id-1.5.8.8.14.1" class="indexterm"></a>281 <a id="id-1.5.8.8.14.2" class="indexterm"></a>282 Ordinary comparison operators yield null (signifying <span class="quote">“<span class="quote">unknown</span>”</span>),283 not true or false, when either input is null. For example,284 <code class="literal">7 = NULL</code> yields null, as does <code class="literal">7 <> NULL</code>. When285 this behavior is not suitable, use the286 <code class="literal">IS [<span class="optional"> NOT </span>] DISTINCT FROM</code> predicates:287</p><pre class="synopsis">288<em class="replaceable"><code>a</code></em> IS DISTINCT FROM <em class="replaceable"><code>b</code></em>289<em class="replaceable"><code>a</code></em> IS NOT DISTINCT FROM <em class="replaceable"><code>b</code></em>290</pre><p>291 For non-null inputs, <code class="literal">IS DISTINCT FROM</code> is292 the same as the <code class="literal"><></code> operator. However, if both293 inputs are null it returns false, and if only one input is294 null it returns true. Similarly, <code class="literal">IS NOT DISTINCT295 FROM</code> is identical to <code class="literal">=</code> for non-null296 inputs, but it returns true when both inputs are null, and false when only297 one input is null. Thus, these predicates effectively act as though null298 were a normal data value, rather than <span class="quote">“<span class="quote">unknown</span>”</span>.299 </p><p>300 <a id="id-1.5.8.8.15.1" class="indexterm"></a>301 <a id="id-1.5.8.8.15.2" class="indexterm"></a>302 <a id="id-1.5.8.8.15.3" class="indexterm"></a>303 <a id="id-1.5.8.8.15.4" class="indexterm"></a>304 To check whether a value is or is not null, use the predicates:305</p><pre class="synopsis">306<em class="replaceable"><code>expression</code></em> IS NULL307<em class="replaceable"><code>expression</code></em> IS NOT NULL308</pre><p>309 or the equivalent, but nonstandard, predicates:310</p><pre class="synopsis">311<em class="replaceable"><code>expression</code></em> ISNULL312<em class="replaceable"><code>expression</code></em> NOTNULL313</pre><p>314 <a id="id-1.5.8.8.15.7" class="indexterm"></a>315 </p><p>316 Do <span class="emphasis"><em>not</em></span> write317 <code class="literal"><em class="replaceable"><code>expression</code></em> = NULL</code>318 because <code class="literal">NULL</code> is not <span class="quote">“<span class="quote">equal to</span>”</span>319 <code class="literal">NULL</code>. (The null value represents an unknown value,320 and it is not known whether two unknown values are equal.)321 </p><div class="tip"><h3 class="title">Tip</h3><p>322 Some applications might expect that323 <code class="literal"><em class="replaceable"><code>expression</code></em> = NULL</code>324 returns true if <em class="replaceable"><code>expression</code></em> evaluates to325 the null value. It is highly recommended that these applications326 be modified to comply with the SQL standard. However, if that327 cannot be done the <a class="xref" href="runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS">transform_null_equals</a>328 configuration variable is available. If it is enabled,329 <span class="productname">PostgreSQL</span> will convert <code class="literal">x =330 NULL</code> clauses to <code class="literal">x IS NULL</code>.331 </p></div><p>332 If the <em class="replaceable"><code>expression</code></em> is row-valued, then333 <code class="literal">IS NULL</code> is true when the row expression itself is null334 or when all the row's fields are null, while335 <code class="literal">IS NOT NULL</code> is true when the row expression itself is non-null336 and all the row's fields are non-null. Because of this behavior,337 <code class="literal">IS NULL</code> and <code class="literal">IS NOT NULL</code> do not always return338 inverse results for row-valued expressions; in particular, a row-valued339 expression that contains both null and non-null fields will return false340 for both tests. In some cases, it may be preferable to341 write <em class="replaceable"><code>row</code></em> <code class="literal">IS DISTINCT FROM NULL</code>342 or <em class="replaceable"><code>row</code></em> <code class="literal">IS NOT DISTINCT FROM NULL</code>,343 which will simply check whether the overall row value is null without any344 additional tests on the row fields.345 </p><p>346 <a id="id-1.5.8.8.19.1" class="indexterm"></a>347 <a id="id-1.5.8.8.19.2" class="indexterm"></a>348 <a id="id-1.5.8.8.19.3" class="indexterm"></a>349 <a id="id-1.5.8.8.19.4" class="indexterm"></a>350 <a id="id-1.5.8.8.19.5" class="indexterm"></a>351 <a id="id-1.5.8.8.19.6" class="indexterm"></a>352 Boolean values can also be tested using the predicates353</p><pre class="synopsis">354<em class="replaceable"><code>boolean_expression</code></em> IS TRUE355<em class="replaceable"><code>boolean_expression</code></em> IS NOT TRUE356<em class="replaceable"><code>boolean_expression</code></em> IS FALSE357<em class="replaceable"><code>boolean_expression</code></em> IS NOT FALSE358<em class="replaceable"><code>boolean_expression</code></em> IS UNKNOWN359<em class="replaceable"><code>boolean_expression</code></em> IS NOT UNKNOWN360</pre><p>361 These will always return true or false, never a null value, even when the362 operand is null.363 A null input is treated as the logical value <span class="quote">“<span class="quote">unknown</span>”</span>.364 Notice that <code class="literal">IS UNKNOWN</code> and <code class="literal">IS NOT UNKNOWN</code> are365 effectively the same as <code class="literal">IS NULL</code> and366 <code class="literal">IS NOT NULL</code>, respectively, except that the input367 expression must be of Boolean type.368 </p><p>369 Some comparison-related functions are also available, as shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-FUNC-TABLE" title="Table 9.3. Comparison Functions">Table 9.3</a>.370 </p><div class="table" id="FUNCTIONS-COMPARISON-FUNC-TABLE"><p class="title"><strong>Table 9.3. Comparison Functions</strong></p><div class="table-contents"><table class="table" summary="Comparison Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">371 Function372 </p>373 <p>374 Description375 </p>376 <p>377 Example(s)378 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">379 <a id="id-1.5.8.8.21.2.2.1.1.1.1" class="indexterm"></a>380 <code class="function">num_nonnulls</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )381 → <code class="returnvalue">integer</code>382 </p>383 <p>384 Returns the number of non-null arguments.385 </p>386 <p>387 <code class="literal">num_nonnulls(1, NULL, 2)</code>388 → <code class="returnvalue">2</code>389 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">390 <a id="id-1.5.8.8.21.2.2.2.1.1.1" class="indexterm"></a>391 <code class="function">num_nulls</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )392 → <code class="returnvalue">integer</code>393 </p>394 <p>395 Returns the number of null arguments.396 </p>397 <p>398 <code class="literal">num_nulls(1, NULL, 2)</code>399 → <code class="returnvalue">1</code>400 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-logical.html" title="9.1. Logical 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-math.html" title="9.3. Mathematical Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.1. Logical 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.3. Mathematical Functions and Operators</td></tr></table></div></body></html>