- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

2.1.6 Lexical Precedence

Table 2-2 shows the precedence and associativity of the operators in PostgreSQL. Most operators have the same precedence and are left-associative. The precedence and associativity of the operators is hard-wired into the parser. This can 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 2-2: Operator Precedence (decreasing)
Operator/Element Associativity Description
. left table/column name separator
:: left PostgreSQL-style typecast
[ ] left array element selection
- right unary minus
^ left exponentiation
* / % left multiplication, division, modulo
+ - left addition, subtraction
ISNULL test for null
NOTNULL test for not null
(any other) left all other native and user-defined operators
IN set membership
BETWEEN range containment
OVERLAPS time interval overlap
LIKE ILIKE SIMILAR 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.

When a schema-qualified operator name is used in the OPERATOR syntax, as for example in:

SELECT 3 OPERATOR(pg_catalog.+) 4;

the OPERATOR construct is taken to have the default precedence shown in Table 2-2 for “any other” operator. This is true no matter which specific operator appears inside OPERATOR().

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition