When utilizing several operators in large expressions, it can be helpful to know in what order PostgreSQL processes
operators. It is not, as you might think, strictly from left to right. If not properly understood, the order of execution
can introduce potential for accidental side-effects, such as those shown in Example 5-18.
Example 5-18. Operator precedence
booktown=#
SELECT 60 + 12 * 5 AS "sixty plus twelve times five",
booktown-#
12 + 60 * 5 AS "twelve plus sixty times five";
sixty plus twelve times five | twelve plus sixty times five
------------------------------+------------------------------
120 | 312
(1 row)
As you can see by the two column values returned in Example 5-18, the use of several
operators without parentheses to enforce precedence can return very different results, despite the same numbers being
manipulated in only a slightly different order. In this example, the multiplication is actually executed first (regardless
of the fact that the addition sign (+) precedes it sequentially, from left to
right).
Table 5-8 lists, in order of PostgreSQL's execution from the top down, the precedence
of each group of operators.
Table 5-8. Operator precedence
| Operator |
Usage |
Description |
|
::
|
value::type
|
Explicit typecast
|
|
[ ]
|
value[
index
]
|
Array element index
|
|
.
|
table.column
|
Table and column name separator
|
|
-
|
-
value
|
Unary minus
|
|
^
|
value
^
power
|
Exponent
|
|
* / %
|
value1
*
value2
|
Multiplication, division, and modulus
|
|
+ -
|
value1
+
value2
|
Addition and subtraction
|
|
IS
|
value
IS
boolean
|
Compares against true or false
|
|
ISNULL
|
value
ISNULL
|
Compares against NULL
|
|
IS NOT NULL
|
value
IS NOT NULL
|
Checks for
value
inequivalent to NULL
|
|
Other
|
Variable
|
Includes all other native and user-defined character operators
|
|
IN
|
value
IN
set
|
Checks for membership of
value
in
set
|
|
BETWEEN
|
value
BETWEEN
a
AND
b
|
Checks for
value
in range between values
a
and
b
|
|
LIKE, ILIKE
|
string
LIKE
comparison
|
Checks for matching pattern
comparison
in
string
|
|
< > <= >=
|
value1
<
value2
|
Quantity comparisons for less than, greater than, less than or equal to, and greater than or equal to.
|
|
=
|
value1
=
value2
|
Equality comparison
|
|
NOT
|
NOT
value
|
Logical NOT inversion
|
|
AND
|
value1
AND
value2
|
Logical AND conjunction
|
|
OR
|
value1
OR
value2
|
Logical OR conjunction
|
Note: The operator precedence listed in Table 5-8 applies to user-defined operators that
have the same character sequence as built-in operators. For example, if you define the plus symbol
(+) operator for your own user-defined data type, it has the same precedence as the
built in plus (+) operator, regardless of its function.
