Unsupported versions: 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

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
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.