The precedence and associativity of the operators is hard-wired
into the parser. Most operators have the same precedence and are
left-associative. This may lead to non-intuitive behavior. For
example, the Boolean operators "<" and ">" have a different
precedence than the Boolean operators "<=" and ">=". Also,
you will sometimes need to add parentheses when using combinations
of binary and unary operators. For instance
will be parsed as
because the parser has no idea—until it is too late—that
"!" is defined as a postfix operator, not an infix one.
To get the desired behavior in this case, you must write
This is due to the fact that the set of operators in PostgreSQL can be
extended by the user.
Table 1-1. Operator Precedence (decreasing)
Operator/Element | Associativity | Description |
---|
:: | left | PostgreSQL-style typecast |
[ ] | left | array element selection |
. | left | table/column name separator |
- | right | unary minus |
^ | left | exponentiation |
* / % | left | multiplication, division, modulo |
+ - | left | addition, subtraction |
IS | | test for TRUE, FALSE, NULL |
ISNULL | | test for NULL |
NOTNULL | | test for NOT NULL |
(any other) | left | all other native and user-defined operators |
IN | | set membership |
BETWEEN | | containment |
OVERLAPS | | time interval overlap |
LIKE ILIKE | | string pattern matching |
< > | | less than, greater than |
= | right | equality, assignment |
NOT | right | logical negation |
AND | left | logical conjunction |
OR | left | logical disjunction |
Note that the operator precedence rules also apply to user-defined
operators that have the same names as the built-in operators.
For example, if you define a
"+" operator for some custom data type, it will have
the same precedence as the built-in "+" operator, no
matter what yours does.