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.
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)
|UNION||left||SQL select construct|
|[ ]||left||array delimiters|
|;||left||statement termination, logarithm|
||||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|
|LIKE||string pattern matching|
|< >||boolean inequality|