diff options
Diffstat (limited to 'doc/src/sgml/html/functions-comparison.html')
| -rw-r--r-- | doc/src/sgml/html/functions-comparison.html | 400 |
1 files changed, 400 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-comparison.html b/doc/src/sgml/html/functions-comparison.html new file mode 100644 index 0000000..de26d89 --- /dev/null +++ b/doc/src/sgml/html/functions-comparison.html @@ -0,0 +1,400 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.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 V1.79.1" /><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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 13.4 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></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</h2></div></div></div><a id="id-1.5.8.8.2" class="indexterm"></a><p> + 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>. + </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> + <em class="replaceable"><code>datatype</code></em> <code class="literal"><</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Less than</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal">></code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Greater than</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal"><=</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Less than or equal to</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal">>=</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Greater than or equal to</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal">=</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Equal</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal"><></code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Not equal</td></tr><tr><td> + <em class="replaceable"><code>datatype</code></em> <code class="literal">!=</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </td><td>Not equal</td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p> + <code class="literal"><></code> is the standard SQL notation for <span class="quote">“<span class="quote">not + equal</span>”</span>. <code class="literal">!=</code> is an alias, which is converted + to <code class="literal"><></code> at a very early stage of parsing. + Hence, it is not possible to implement <code class="literal">!=</code> + and <code class="literal"><></code> operators that do different things. + </p></div><p> + These comparison operators are available for all built-in data types + that have a natural ordering, including numeric, string, and date/time + types. In addition, arrays, composite types, and ranges can be compared + if their component data types are comparable. + </p><p> + It is usually possible to compare values of related data + types as well; for example <code class="type">integer</code> <code class="literal">></code> + <code class="type">bigint</code> will work. Some cases of this sort are implemented + directly by <span class="quote">“<span class="quote">cross-type</span>”</span> comparison operators, but if no + such operator is available, the parser will coerce the less-general type + to the more-general type and apply the latter's comparison operator. + </p><p> + As shown above, all comparison operators are binary operators that + return values of type <code class="type">boolean</code>. Thus, expressions like + <code class="literal">1 < 2 < 3</code> are not valid (because there is + no <code class="literal"><</code> operator to compare a Boolean value with + <code class="literal">3</code>). Use the <code class="literal">BETWEEN</code> predicates + shown below to perform range tests. + </p><p> + 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 like + operators, but have special syntax mandated by the SQL standard. + </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"> + Predicate + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <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> + → <code class="returnvalue">boolean</code> + </p> + <p> + Between (inclusive of the range endpoints). + </p> + <p> + <code class="literal">2 BETWEEN 1 AND 3</code> + → <code class="returnvalue">t</code> + </p> + <p> + <code class="literal">2 BETWEEN 3 AND 1</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <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> + → <code class="returnvalue">boolean</code> + </p> + <p> + Not between (the negation of <code class="literal">BETWEEN</code>). + </p> + <p> + <code class="literal">2 NOT BETWEEN 1 AND 3</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <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> + → <code class="returnvalue">boolean</code> + </p> + <p> + Between, after sorting the two endpoint values. + </p> + <p> + <code class="literal">2 BETWEEN SYMMETRIC 3 AND 1</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <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> + → <code class="returnvalue">boolean</code> + </p> + <p> + Not between, after sorting the two endpoint values. + </p> + <p> + <code class="literal">2 NOT BETWEEN SYMMETRIC 3 AND 1</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">IS DISTINCT FROM</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </p> + <p> + Not equal, treating null as a comparable value. + </p> + <p> + <code class="literal">1 IS DISTINCT FROM NULL</code> + → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>) + </p> + <p> + <code class="literal">NULL IS DISTINCT FROM NULL</code> + → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NOT DISTINCT FROM</code> <em class="replaceable"><code>datatype</code></em> + → <code class="returnvalue">boolean</code> + </p> + <p> + Equal, treating null as a comparable value. + </p> + <p> + <code class="literal">1 IS NOT DISTINCT FROM NULL</code> + → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>) + </p> + <p> + <code class="literal">NULL IS NOT DISTINCT FROM NULL</code> + → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NULL</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether value is null. + </p> + <p> + <code class="literal">1.5 IS NULL</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">IS NOT NULL</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether value is not null. + </p> + <p> + <code class="literal">'null' IS NOT NULL</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">ISNULL</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether value is null (nonstandard syntax). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>datatype</code></em> <code class="literal">NOTNULL</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether value is not null (nonstandard syntax). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS TRUE</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields true. + </p> + <p> + <code class="literal">true IS TRUE</code> + → <code class="returnvalue">t</code> + </p> + <p> + <code class="literal">NULL::boolean IS TRUE</code> + → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS NOT TRUE</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields false or unknown. + </p> + <p> + <code class="literal">true IS NOT TRUE</code> + → <code class="returnvalue">f</code> + </p> + <p> + <code class="literal">NULL::boolean IS NOT TRUE</code> + → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS FALSE</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields false. + </p> + <p> + <code class="literal">true IS FALSE</code> + → <code class="returnvalue">f</code> + </p> + <p> + <code class="literal">NULL::boolean IS FALSE</code> + → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS NOT FALSE</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields true or unknown. + </p> + <p> + <code class="literal">true IS NOT FALSE</code> + → <code class="returnvalue">t</code> + </p> + <p> + <code class="literal">NULL::boolean IS NOT FALSE</code> + → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS UNKNOWN</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields unknown. + </p> + <p> + <code class="literal">true IS UNKNOWN</code> + → <code class="returnvalue">f</code> + </p> + <p> + <code class="literal">NULL::boolean IS UNKNOWN</code> + → <code class="returnvalue">t</code> (rather than <code class="literal">NULL</code>) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">boolean</code> <code class="literal">IS NOT UNKNOWN</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Test whether boolean expression yields true or false. + </p> + <p> + <code class="literal">true IS NOT UNKNOWN</code> + → <code class="returnvalue">t</code> + </p> + <p> + <code class="literal">NULL::boolean IS NOT UNKNOWN</code> + → <code class="returnvalue">f</code> (rather than <code class="literal">NULL</code>) + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <a id="id-1.5.8.8.11.1" class="indexterm"></a> + <a id="id-1.5.8.8.11.2" class="indexterm"></a> + The <code class="token">BETWEEN</code> predicate simplifies range tests: +</p><pre class="synopsis"> +<em class="replaceable"><code>a</code></em> BETWEEN <em class="replaceable"><code>x</code></em> AND <em class="replaceable"><code>y</code></em> +</pre><p> + is equivalent to +</p><pre class="synopsis"> +<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> +</pre><p> + Notice that <code class="token">BETWEEN</code> treats the endpoint values as included + in the range. + <code class="literal">BETWEEN SYMMETRIC</code> is like <code class="literal">BETWEEN</code> + except there is no requirement that the argument to the left of + <code class="literal">AND</code> be less than or equal to the argument on the right. + If it is not, those two arguments are automatically swapped, so that + a nonempty range is always implied. + </p><p> + The various variants of <code class="literal">BETWEEN</code> are implemented in + terms of the ordinary comparison operators, and therefore will work for + any data type(s) that can be compared. + </p><div class="note"><h3 class="title">Note</h3><p> + The use of <code class="literal">AND</code> in the <code class="literal">BETWEEN</code> + syntax creates an ambiguity with the use of <code class="literal">AND</code> as a + logical operator. To resolve this, only a limited set of expression + types are allowed as the second argument of a <code class="literal">BETWEEN</code> + clause. If you need to write a more complex sub-expression + in <code class="literal">BETWEEN</code>, write parentheses around the + sub-expression. + </p></div><p> + <a id="id-1.5.8.8.14.1" class="indexterm"></a> + <a id="id-1.5.8.8.14.2" class="indexterm"></a> + Ordinary comparison operators yield null (signifying <span class="quote">“<span class="quote">unknown</span>”</span>), + not true or false, when either input is null. For example, + <code class="literal">7 = NULL</code> yields null, as does <code class="literal">7 <> NULL</code>. When + this behavior is not suitable, use the + <code class="literal">IS [<span class="optional"> NOT </span>] DISTINCT FROM</code> predicates: +</p><pre class="synopsis"> +<em class="replaceable"><code>a</code></em> IS DISTINCT FROM <em class="replaceable"><code>b</code></em> +<em class="replaceable"><code>a</code></em> IS NOT DISTINCT FROM <em class="replaceable"><code>b</code></em> +</pre><p> + For non-null inputs, <code class="literal">IS DISTINCT FROM</code> is + the same as the <code class="literal"><></code> operator. However, if both + inputs are null it returns false, and if only one input is + null it returns true. Similarly, <code class="literal">IS NOT DISTINCT + FROM</code> is identical to <code class="literal">=</code> for non-null + inputs, but it returns true when both inputs are null, and false when only + one input is null. Thus, these predicates effectively act as though null + were a normal data value, rather than <span class="quote">“<span class="quote">unknown</span>”</span>. + </p><p> + <a id="id-1.5.8.8.15.1" class="indexterm"></a> + <a id="id-1.5.8.8.15.2" class="indexterm"></a> + <a id="id-1.5.8.8.15.3" class="indexterm"></a> + <a id="id-1.5.8.8.15.4" class="indexterm"></a> + To check whether a value is or is not null, use the predicates: +</p><pre class="synopsis"> +<em class="replaceable"><code>expression</code></em> IS NULL +<em class="replaceable"><code>expression</code></em> IS NOT NULL +</pre><p> + or the equivalent, but nonstandard, predicates: +</p><pre class="synopsis"> +<em class="replaceable"><code>expression</code></em> ISNULL +<em class="replaceable"><code>expression</code></em> NOTNULL +</pre><p> + <a id="id-1.5.8.8.15.7" class="indexterm"></a> + </p><p> + Do <span class="emphasis"><em>not</em></span> write + <code class="literal"><em class="replaceable"><code>expression</code></em> = NULL</code> + because <code class="literal">NULL</code> is not <span class="quote">“<span class="quote">equal to</span>”</span> + <code class="literal">NULL</code>. (The null value represents an unknown value, + and it is not known whether two unknown values are equal.) + </p><div class="tip"><h3 class="title">Tip</h3><p> + Some applications might expect that + <code class="literal"><em class="replaceable"><code>expression</code></em> = NULL</code> + returns true if <em class="replaceable"><code>expression</code></em> evaluates to + the null value. It is highly recommended that these applications + be modified to comply with the SQL standard. However, if that + cannot be done the <a class="xref" href="runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS">transform_null_equals</a> + configuration variable is available. If it is enabled, + <span class="productname">PostgreSQL</span> will convert <code class="literal">x = + NULL</code> clauses to <code class="literal">x IS NULL</code>. + </p></div><p> + If the <em class="replaceable"><code>expression</code></em> is row-valued, then + <code class="literal">IS NULL</code> is true when the row expression itself is null + or when all the row's fields are null, while + <code class="literal">IS NOT NULL</code> is true when the row expression itself is non-null + and all the row's fields are non-null. Because of this behavior, + <code class="literal">IS NULL</code> and <code class="literal">IS NOT NULL</code> do not always return + inverse results for row-valued expressions; in particular, a row-valued + expression that contains both null and non-null fields will return false + for both tests. In some cases, it may be preferable to + write <em class="replaceable"><code>row</code></em> <code class="literal">IS DISTINCT FROM NULL</code> + or <em class="replaceable"><code>row</code></em> <code class="literal">IS NOT DISTINCT FROM NULL</code>, + which will simply check whether the overall row value is null without any + additional tests on the row fields. + </p><p> + <a id="id-1.5.8.8.19.1" class="indexterm"></a> + <a id="id-1.5.8.8.19.2" class="indexterm"></a> + <a id="id-1.5.8.8.19.3" class="indexterm"></a> + <a id="id-1.5.8.8.19.4" class="indexterm"></a> + <a id="id-1.5.8.8.19.5" class="indexterm"></a> + <a id="id-1.5.8.8.19.6" class="indexterm"></a> + Boolean values can also be tested using the predicates +</p><pre class="synopsis"> +<em class="replaceable"><code>boolean_expression</code></em> IS TRUE +<em class="replaceable"><code>boolean_expression</code></em> IS NOT TRUE +<em class="replaceable"><code>boolean_expression</code></em> IS FALSE +<em class="replaceable"><code>boolean_expression</code></em> IS NOT FALSE +<em class="replaceable"><code>boolean_expression</code></em> IS UNKNOWN +<em class="replaceable"><code>boolean_expression</code></em> IS NOT UNKNOWN +</pre><p> + These will always return true or false, never a null value, even when the + operand is null. + A null input is treated as the logical value <span class="quote">“<span class="quote">unknown</span>”</span>. + Notice that <code class="literal">IS UNKNOWN</code> and <code class="literal">IS NOT UNKNOWN</code> are + effectively the same as <code class="literal">IS NULL</code> and + <code class="literal">IS NOT NULL</code>, respectively, except that the input + expression must be of Boolean type. + </p><p> + 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>. + </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"> + Function + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.8.21.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">num_nonnulls</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of non-null arguments. + </p> + <p> + <code class="literal">num_nonnulls(1, NULL, 2)</code> + → <code class="returnvalue">2</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.8.21.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">num_nulls</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of null arguments. + </p> + <p> + <code class="literal">num_nulls(1, NULL, 2)</code> + → <code class="returnvalue">1</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.3. Mathematical Functions and Operators</td></tr></table></div></body></html>
\ No newline at end of file |