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
SELECT 5 ! - 6;will be parsed as
SELECT 5 ! (- 6);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
SELECT (5 !) - 6;This is the price one pays for extensibility.
Table 1-1. Operator Precedence (decreasing)
Operator/Element | Associativity | Description |
---|---|---|
:: | left | Postgres-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 mentioned above. 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.