Chapter 3. Operators and Functions

PostgreSQL supports left unary, right unary and binary operators. Operators can be overloaded; that is, the same operator name can be used for different operators that have different numbers and types of arguments. If there is an ambiguous situation and the system cannot determine the correct operator to use, it will return an error. You may have to typecast the left and/or right operands to help it understand which operator you meant to use.

PostgreSQL provides a large number of functions and operators for the built-in data types. Users can also define their own functions and operators, as described in Chapter 4. The psql commands \df and \do can be used to show the list of all available functions and operators, respectively.

If you are concerned about portability then take note that most of the functions and operators described in this chapter, with the exception of the most trivial arithmetic and comparison operators and some explicitly marked functions, are not specified by the SQL standard.

Logical Operators

The logical operators available are:

SQL uses a three-valued Boolean logic where NULL represents "unknown". Observe the following truth tables:

aba AND ba OR b
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULL

aNOT a
TRUEFALSE
FALSETRUE
NULLNULL