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 | 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, UNKNOWN, 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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.