Unsupported versions: 7.2 / 7.1

1.4. Lexical Precedence

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
ISNULL   test for 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.