Operators are usually easily distinguished from functions because they use punctuation, e.g. -, *, /, rather than words with letters and
numbers. There are two popular types of operators, unary and binary. Unary operators take one argument, e.g. -3 (minus), !false (not).
Binary operators take two arguments, one on each side of the operator, e.g. 2 + 3 (addition), ‘a’ || ‘b’ (concatenation). (C-based
languages often also support the ternary operator ?:.)

Perhaps surprisingly, in Postgres comparisons are also operators, e.g. a = 3 (equality), to_tsvector(‘hello bye’) @@ ‘hello’
(full text search). Comparison operators return true, false, or
NULL, and their return values are processed by surrounding
keywords or clauses, e.g. WHERE, AND. For example, to process WHERE col1 = 10 + 1 AND col2 > 5, 10
+ 1
is processed with +, col1 = 11 is then processed with =, col2 > 5 is processed with >, the results processed by
AND, and its return value is passed to the query processor via WHERE. Just as you can type \dfS to see all defined
functions in psql, and \dTS to see all defined data types, \doS shows
all defined operators.

You might think of operators as distinct from functions, but internally operator behavior is bound directly to functions. For example,
factorial contains both operator and function definitions. The factorial operator, !, is actually the only Postgres-supplied
left-associative unary operator:

Continue Reading »