<?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 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><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 11.5 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.7.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> <code class="literal"><</code> </td><td>less than</td></tr><tr><td> <code class="literal">></code> </td><td>greater than</td></tr><tr><td> <code class="literal"><=</code> </td><td>less than or equal to</td></tr><tr><td> <code class="literal">>=</code> </td><td>greater than or equal to</td></tr><tr><td> <code class="literal">=</code> </td><td>equal</td></tr><tr><td> <code class="literal"><></code> or <code class="literal">!=</code> </td><td>not equal</td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
The <code class="literal">!=</code> operator is converted to
<code class="literal"><></code> in the parser stage. It is not
possible to implement <code class="literal">!=</code> and
<code class="literal"><></code> operators that do different things.
</p></div><p>
Comparison operators are available for all relevant data types.
All comparison operators are binary operators that
return values of type <code class="type">boolean</code>; 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>).
</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 /><col /></colgroup><thead><tr><th>Predicate</th><th>Description</th></tr></thead><tbody><tr><td> <em class="replaceable"><code>a</code></em> <code class="literal">BETWEEN</code> <em class="replaceable"><code>x</code></em> <code class="literal">AND</code> <em class="replaceable"><code>y</code></em> </td><td>between</td></tr><tr><td> <em class="replaceable"><code>a</code></em> <code class="literal">NOT BETWEEN</code> <em class="replaceable"><code>x</code></em> <code class="literal">AND</code> <em class="replaceable"><code>y</code></em> </td><td>not between</td></tr><tr><td> <em class="replaceable"><code>a</code></em> <code class="literal">BETWEEN SYMMETRIC</code> <em class="replaceable"><code>x</code></em> <code class="literal">AND</code> <em class="replaceable"><code>y</code></em> </td><td>between, after sorting the comparison values</td></tr><tr><td> <em class="replaceable"><code>a</code></em> <code class="literal">NOT BETWEEN SYMMETRIC</code> <em class="replaceable"><code>x</code></em> <code class="literal">AND</code> <em class="replaceable"><code>y</code></em> </td><td>not between, after sorting the comparison values</td></tr><tr><td> <em class="replaceable"><code>a</code></em> <code class="literal">IS DISTINCT FROM</code> <em class="replaceable"><code>b</code></em> </td><td>not equal, treating null like an ordinary value</td></tr><tr><td><em class="replaceable"><code>a</code></em> <code class="literal">IS NOT DISTINCT FROM</code> <em class="replaceable"><code>b</code></em></td><td>equal, treating null like an ordinary value</td></tr><tr><td> <em class="replaceable"><code>expression</code></em> <code class="literal">IS NULL</code> </td><td>is null</td></tr><tr><td> <em class="replaceable"><code>expression</code></em> <code class="literal">IS NOT NULL</code> </td><td>is not null</td></tr><tr><td> <em class="replaceable"><code>expression</code></em> <code class="literal">ISNULL</code> </td><td>is null (nonstandard syntax)</td></tr><tr><td> <em class="replaceable"><code>expression</code></em> <code class="literal">NOTNULL</code> </td><td>is not null (nonstandard syntax)</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS TRUE</code> </td><td>is true</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS NOT TRUE</code> </td><td>is false or unknown</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS FALSE</code> </td><td>is false</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS NOT FALSE</code> </td><td>is true or unknown</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS UNKNOWN</code> </td><td>is unknown</td></tr><tr><td> <em class="replaceable"><code>boolean_expression</code></em> <code class="literal">IS NOT UNKNOWN</code> </td><td>is true or false</td></tr></tbody></table></div></div><br class="table-break" /><p>
<a id="id-1.5.8.7.9.1" 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">NOT BETWEEN</code> does the opposite comparison:
</p><pre class="synopsis">
<em class="replaceable"><code>a</code></em> NOT 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> OR <em class="replaceable"><code>a</code></em> > <em class="replaceable"><code>y</code></em>
</pre><p>
<a id="id-1.5.8.7.9.9" class="indexterm"></a>
<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>
<a id="id-1.5.8.7.10.1" class="indexterm"></a>
<a id="id-1.5.8.7.10.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.7.11.1" class="indexterm"></a>
<a id="id-1.5.8.7.11.2" class="indexterm"></a>
<a id="id-1.5.8.7.11.3" class="indexterm"></a>
<a id="id-1.5.8.7.11.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.7.11.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.7.15.1" class="indexterm"></a>
<a id="id-1.5.8.7.15.2" class="indexterm"></a>
<a id="id-1.5.8.7.15.3" class="indexterm"></a>
<a id="id-1.5.8.7.15.4" class="indexterm"></a>
<a id="id-1.5.8.7.15.5" class="indexterm"></a>
<a id="id-1.5.8.7.15.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 /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Description</th><th>Example</th><th>Example Result</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.7.17.2.2.1.1.1" class="indexterm"></a>
<code class="literal">num_nonnulls(VARIADIC "any")</code>
</td><td>returns the number of non-null arguments</td><td><code class="literal">num_nonnulls(1, NULL, 2)</code></td><td><code class="literal">2</code></td></tr><tr><td>
<a id="id-1.5.8.7.17.2.2.2.1.1" class="indexterm"></a>
<code class="literal">num_nulls(VARIADIC "any")</code>
</td><td>returns the number of null arguments</td><td><code class="literal">num_nulls(1, NULL, 2)</code></td><td><code class="literal">1</code></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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-math.html">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">Home</a></td><td width="40%" align="right" valign="top"> 9.3. Mathematical Functions and Operators</td></tr></table></div></body></html>
