Operator Evaluation
Check for an exact match in the pg_operator system catalog.
If one argument of a binary operator is unknown, then assume it is the same type as the other argument.
Reverse the arguments, and look for an exact match with an operator which points to itself as being commutative. If found, then reverse the arguments in the parse tree and use this operator.
Look for the best match.
Make a list of all operators of the same name.
If only one operator is in the list, use it if the input type can be coerced, and throw an error if the type cannot be coerced.
Keep all operators with the most explicit matches for types. Keep all if there are no explicit matches and move to the next step. If only one candidate remains, use it if the type can be coerced.
If any input arguments are "unknown", categorize the input candidates as boolean, numeric, string, geometric, or user-defined. If there is a mix of categories, or more than one user-defined type, throw an error because the correct choice cannot be deduced without more clues. If only one category is present, then assign the "preferred type" to the input column which had been previously "unknown".
Choose the candidate with the most exact type matches, and which matches the "preferred type" for each column category from the previous step. If there is still more than one candidate, or if there are none, then throw an error.
There is only one exponentiation operator defined in the catalog, and it takes float8 arguments. The scanner assigns an initial type of int4 to both arguments of this query expression:
tgl=> select 2 ^ 3 AS "Exp"; Exp --- 8 (1 row)So the parser does a type conversion on both operands and the query is equivalent to
tgl=> select float8(2) ^ float8(3) AS "Exp"; Exp --- 8 (1 row)or
tgl=> select 2.0 ^ 3.0 AS "Exp"; Exp --- 8 (1 row)
Note: This last form has the least overhead, since no functions are called to do implicit type conversion. This is not an issue for small queries, but may have an impact on the performance of queries involving large tables.
A string-like syntax is used for working with string types as well as for working with complex extended types. Strings with unspecified type are matched with likely operator candidates.
One unspecified argument:
tgl=> SELECT text 'abc' || 'def' AS "Text and Unknown"; Text and Unknown ---------------- abcdef (1 row)
In this case the parser looks to see if there is an operator taking text for both arguments. Since there is, it assumes that the second argument should be interpreted as of type text.
Concatenation on unspecified types:
tgl=> SELECT 'abc' || 'def' AS "Unspecified"; Unspecified ----------- abcdef (1 row)
In this case there is no initial hint for which type to use, since no types are specified in the query. So, the parser looks for all candidate operators and finds that all arguments for all the candidates are string types. It chooses the "preferred type" for strings, text, for this query.
Note: If a user defines a new type and defines an operator “||” to work with it, then this query would no longer succeed as written. The parser would now have candidate types from two categories, and could not decide which to use.
This example illustrates an interesting result. Traditionally, the factorial operator is defined for integers only. The Postgres operator catalog has only one entry for factorial, taking an integer operand. If given a non-integer numeric argument, Postgres will try to convert that argument to an integer for evaluation of the factorial.
tgl=> select (4.3 !);
?column?
--------
24
(1 row)
Note: Of course, this leads to a mathematically suspect result, since in principle the factorial of a non-integer is not defined. However, the role of a database is not to teach mathematics, but to be a tool for data manipulation. If a user chooses to take the factorial of a floating point number, Postgres will try to oblige.