Chapter 9. Operators

Table of Contents
Lexical Precedence
General Operators
Numerical Operators
Geometric Operators
Time Interval Operators
IP V4 Operators
IP V4 Operators

Describes the built-in operators available in Postgres.

Postgres provides a large number of built-in operators on system types. These operators are declared in the system catalog pg_operator. Every entry in pg_operator includes the name of the procedure that implements the operator and the class OIDs of the input and output types.

To view all variations of the “||” string concatenation operator, try

    SELECT oprleft, oprright, oprresult, oprcode
    FROM pg_operator WHERE oprname = '||';

oprleft|oprright|oprresult|oprcode
-------+--------+---------+-------
     25|      25|       25|textcat
   1042|    1042|     1042|textcat
   1043|    1043|     1043|textcat
(3 rows)

Users may invoke operators using the operator name, as in:

select * from emp where salary < 40000;
Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as:
select * from emp where int4lt(salary, 40000);

psql has a command (\dd) to show these operators.

Lexical Precedence

Operators have a precedence which is currently hardcoded into the parser. Most operators have the same precedence and are non-associative. This may lead to non-intuitive behavior; for example the boolean operators "<" and ">" have a different precedence that the boolean operators "<=" and ">=".

Table 9-1. Operator Ordering (decreasing precedence)

ElementPrecedenceDescription
UNIONleftSQL select construct
:: Postgres typecasting
[ ]leftarray delimiters
.lefttable/column delimiter
-rightunary minus
;leftstatement termination, logarithm
:rightexponentiation
|leftstart of interval
* /leftmultiplication, division
+ -leftaddition, subtraction
IS test for TRUE, FALSE, NULL
ISNULL test for NULL
NOTNULL test for NOT NULL
(all other operators) native and user-defined
IN set membership
BETWEEN containment
LIKE string pattern matching
< > boolean inequality
=rightequality
NOTrightnegation
ANDleftlogical intersection
ORleftlogical union