The usual comparison operators are available, shown in Table 6-1.
Table 6-1. Comparison Operators
| Operator | Description |
|---|
| < | less than |
| > | greater than |
| <= | less than or equal to |
| >= | greater than or equal to |
| = | equal |
| <> or != | not equal |
Note: The != operator is converted to
<> in the parser stage. It is not
possible to implement != and
<> operators that do different things.
Comparison operators are available for all data types where this
makes sense. All comparison operators are binary operators that
return values of type boolean; expressions like
1 < 2 < 3 are not valid (because there is
no < operator to compare a Boolean value with
3).
In addition to the comparison operators, the special
BETWEEN construct is available.
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
Similarly,
a NOT BETWEEN x AND y
is equivalent to
a < x OR a > y
There is no difference between the two respective forms apart from
the CPU cycles required to rewrite the first one
into the second one internally.
To check whether a value is or is not null, use the constructs
expression IS NULL
expression IS NOT NULL
or the equivalent, but nonstandard, constructs
expression ISNULL
expression NOTNULL
Do not write
expression = NULL
because NULL is not "equal to"
NULL. (The null value represents an unknown value,
and it is not known whether two unknown values are equal.)
Some applications may (incorrectly) require that
expression = NULL
returns true if expression evaluates to
the null value. To support these applications, the run-time option
transform_null_equals can be turned on (e.g.,
SET transform_null_equals TO ON;).
PostgreSQL will then convert
x = NULL clauses to
x IS NULL. This was
the default behavior in releases 6.5 through 7.1.
Boolean values can also be tested using the constructs
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
These are similar to IS NULL in that they 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 "unknown".
