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 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 |