Unsupported versions: 7.0 / 6.5 / 6.4

Chapter 4. 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 = '||';

     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 left-associative. This may lead to non-intuitive behavior; for example the boolean operators "<" and ">" have a different precedence than the boolean operators "<=" and ">=".

Table 4-1. Operator Ordering (decreasing precedence)

Element Precedence Description
UNION left SQL select construct
::   Postgres typecasting
[ ] left array delimiters
. left table/column delimiter
- right unary minus
: right exponentiation
| left start of interval
* / % left multiplication, division, modulo
+ - left addition, subtraction
IS   test for TRUE, FALSE, NULL
ISNULL   test for NULL
(all other operators)   native and user-defined
IN   set membership
BETWEEN   containment
OVERLAPS   time interval overlap
LIKE   string pattern matching
< >   inequality
= right equality
NOT right logical negation
AND left logical intersection
OR left logical union