September 26, 2024: PostgreSQL 17 Released!
Unsupported versions: 7.0 / 6.5 / 6.4
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.

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 = '||';

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 left-associative. This may lead to non-intuitive behavior; for example the boolean operators "<" and ">" have a different precedence that 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
; left statement termination, logarithm
: right exponentiation
| left start of interval
* / % left multiplication, division
+ - left addition, 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
= right equality
NOT right negation
AND left logical intersection
OR left logical union