1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.2.Comparison Operators</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="next" href="functions-math.html" title="9.3.Mathematical Functions and Operators">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-comparison"></a>9.2.Comparison Operators</h2></div></div></div>
<a name="id594261"></a><p> The usual comparison operators are available, shown in <a href="functions-comparison.html#functions-comparison-table" title="Table9.1.Comparison Operators">Table9.1, “Comparison Operators”</a>.
</p>
<div class="table">
<a name="functions-comparison-table"></a><p class="title"><b>Table9.1.Comparison Operators</b></p>
<div class="table-contents"><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" style="margin-left: 0.5in; margin-right: 0.5in;">
<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 data types where this
makes sense. 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> <a name="id594456"></a>
In addition to the comparison operators, the special
<code class="token">BETWEEN</code> construct is available.
</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>
Similarly,
</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>
There is no difference between the two respective forms apart from
the <acronym class="acronym">CPU</acronym> cycles required to rewrite the first one
into the second one internally.
<a name="id594546"></a>
<code class="token">BETWEEN SYMMETRIC</code> is the same as <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; the proper range is automatically determined.
</p>
<p> <a name="id594576"></a>
<a name="id594584"></a>
<a name="id594592"></a>
<a name="id594599"></a>
To check whether a value is or is not null, use the constructs
</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, constructs
</p>
<pre class="synopsis"><em class="replaceable"><code>expression</code></em> ISNULL
<em class="replaceable"><code>expression</code></em> NOTNULL</pre>
<p>
<a name="id594636"></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">equal to</span>”
<code class="literal">NULL</code>. (The null value represents an unknown value,
and it is not known whether two unknown values are equal.) This
behavior conforms to the SQL standard.
</p>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> Some applications may 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 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>. This was
the default behavior in <span class="productname">PostgreSQL</span>
releases 6.5 through 7.1.
</p>
</div>
<p> <a name="id594745"></a>
The ordinary comparison operators yield null (signifying “<span class="quote">unknown</span>”)
when either input is null. Another way to do comparisons is with the
<code class="literal">IS DISTINCT FROM</code> construct:
</p>
<pre class="synopsis"><em class="replaceable"><code>expression</code></em> IS DISTINCT FROM <em class="replaceable"><code>expression</code></em></pre>
<p>
For non-null inputs this is the same as the <code class="literal"><></code> operator.
However, when both inputs are null it will return false, and when just
one input is null it will return true. Thus it effectively acts as though
null were a normal data value, rather than “<span class="quote">unknown</span>”.
</p>
<p> <a name="id594796"></a>
<a name="id594804"></a>
<a name="id594811"></a>
<a name="id594819"></a>
<a name="id594827"></a>
<a name="id594834"></a>
Boolean values can also be tested using the constructs
</p>
<pre class="synopsis"><em class="replaceable"><code>expression</code></em> IS TRUE
<em class="replaceable"><code>expression</code></em> IS NOT TRUE
<em class="replaceable"><code>expression</code></em> IS FALSE
<em class="replaceable"><code>expression</code></em> IS NOT FALSE
<em class="replaceable"><code>expression</code></em> IS UNKNOWN
<em class="replaceable"><code>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">unknown</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>
</div></body>
</html>
|
