typeconv-oper

The specific operator that is referenced by an operator expression is determined by the following procedure. Note that this procedure is indirectly affected by operator precedence, because precedence determines which subexpressions serve as inputs to which operators.

Operator type resolution

1. Select candidate operators from the pg_operator system catalog. If an unqualified operator name is used, which is the common case, consider only operators that are visible in the current search path and have matching names and argument counts. If a qualified operator name is given, consider only operators in the specified schema.

1. If the search path finds multiple operators with identical argument types, keep only the one that appears earliest in the path. However, operators with different argument types are treated equally, regardless of their position in the path.

2. Look for an operator that accepts the input argument types exactly. If one is found, use it. There can be at most one exact match among the candidates. The absence of an exact match poses a security risk when you invoke an operator using a qualified name from a schema that allows untrusted users to create objects^[1]. This is an atypical scenario. In such cases, you must cast the arguments to enforce an exact match.

1. If one argument of a binary operator call has the type unknown, assume that it matches the type of the other argument during this check. Calls that involve two unknown inputs or a unary operator with one unknown input will never find a match at this step.

2. If one argument of a binary operator call has the type unknown and the other is a domain type, check whether an operator exists that accepts the domain’s base type for both arguments. If so, use it.

3. Find the best match.

1. Discard candidate operators whose input types do not match and cannot be converted to match. For this purpose, treat unknown literals as convertible to any type. If only one candidate remains, use it. Otherwise, proceed to the next step.

2. If any input argument is a domain type, treat it as its base type for all subsequent steps. This ensures that domains behave like their base types when ambiguous operators are resolved.

3. Examine all remaining candidates and keep those with the most exact input type matches. If no candidate has an exact match, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

4. Examine all remaining candidates and keep those that accept the preferred type from the input data type's category at the most positions that require type conversion. If no candidate accepts a preferred type, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

5. If any input argument has the type unknown, inspect the type categories that the remaining candidates accept at those argument positions. At each position, select the string category if any candidate accepts it. This preference is appropriate because unknown-type literals resemble strings. Otherwise, if all remaining candidates accept the same type category, select that category. If not, raise an error because no further inference is possible. Discard any candidates that do not accept the selected type category. Then, if any candidate accepts a preferred type within that category, discard the candidates that accept non-preferred types at that argument position. If no candidate passes these tests, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.

6. If the call includes both unknown and known-type arguments, and all known-type arguments have the same type, assume that the unknown arguments are also of that type. Then, check which candidates accept that type at the unknown argument positions. If exactly one candidate passes this test, use it. Otherwise, the process fails.

Example 1: Square root operator type resolution

The standard catalog defines only one square root operator (prefix |/), which accepts a double precision argument. In the following query expression, the scanner initially assigns the type integer to the operand:

    SELECT |/ 40 AS "square root of 40";
     square root of 40
    -------------------
     6.324555320336759
    (1 row)

Therefore, the parser performs a type conversion on the operand. The query is equivalent to the following:

    SELECT |/ CAST(40 AS double precision) AS "square root of 40";

Example 2: String concatenation operator type resolution

A string-like syntax is used for both string types and complex extended types. Unspecified-type string literals are matched against possible candidate operators.

The following is an example with one unspecified argument:

    SELECT text 'abc' || 'def' AS "text and unknown";

     text and unknown
    ------------------
     abcdef
    (1 row)

In this case, the parser checks whether an operator exists that accepts text for both arguments. Because one does, it assumes that the second argument should be interpreted as type text.

The following is a concatenation of two unspecified-type literals:

    SELECT 'abc' || 'def' AS "unspecified";

     unspecified
    -------------
     abcdef
    (1 row)

In this case, there is no initial hint about which type to use, because no types are specified in the query. Therefore, the parser examines all candidate operators and finds candidates that accept inputs from both the string and bit-string categories. Because the string category is preferred when available, that category is selected, and its preferred type (text) is used to resolve the unknown-type literals.

Example 3: Absolute value and negation operator type resolution

The database operator catalog contains several entries for the prefix operator @, which implements the absolute value operation for various numeric data types. One of these entries is for the type float8, which is the preferred type in the numeric category. Therefore, the database uses this operator when it encounters an unknown input:

    SELECT @ '-4.5' AS "abs";
     abs
    -----
     4.5
    (1 row)

Here, the system implicitly resolved the unknown-type literal as type float8 before it applied the selected operator. You can verify that float8, and not another type, is used:

    SELECT @ '-4.5e500' AS "abs";

    ERROR:  "-4.5e500" is out of range for type double precision

In contrast, the prefix operator ~ (bitwise NOT) is defined only for integer data types, not for float8. Therefore, if you attempt a similar case with ~, the following error occurs:

    SELECT ~ '20' AS "negation";

    ERROR:  operator is not unique: ~ "unknown"
    HINT:  Could not choose a best candidate operator. You might need to add
    explicit type casts.

This occurs because the system cannot decide which of the several possible ~ operators to select. An explicit cast resolves the ambiguity:

    SELECT ~ CAST('20' AS int8) AS "negation";

     negation
    ----------
          -21
    (1 row)

Example 4: Array containment operator type resolution

The following is another example of resolving an operator with one known and one unknown input:

    SELECT array[1,2] <@ '{1,2,3}' as "is subset";

     is subset
    -----------
     t
    (1 row)

The database operator catalog includes several entries for the infix operator <@, but only two can accept an integer array as the left-hand operand: array containment (anyarray <@ anyarray) and range containment (anyelement <@ anyrange). Because neither of these polymorphic pseudotypes is preferred, the parser cannot resolve the ambiguity based on preference alone. However, step 3.f directs the parser to assume that the unknown-type literal has the same type as the other input, which is an integer array in this case. Only one of the two operators matches this assumption, so the array containment operator is selected. If the range containment operator were chosen, an error would occur because the string does not have the correct format for a range literal.

Example 5: Custom operators on domain types

Users sometimes try to define operators that apply only to a specific domain type. This is possible but less useful than it might seem, because the operator resolution rules are designed to select operators for the domain’s base type. Consider the following example:

    CREATE DOMAIN mytext AS text CHECK(...);
    CREATE FUNCTION mytext_eq_text (mytext, text) RETURNS boolean AS ...;
    CREATE OPERATOR = (procedure=mytext_eq_text, leftarg=mytext, rightarg=text);
    CREATE TABLE mytable (val mytext);

    SELECT * FROM mytable WHERE val = 'foo';

This query will not use the custom operator. The parser first checks for a mytext = mytext operator (step 2.a), but none exists. It then considers the domain’s base type, text, and looks for a text = text operator (step 2.b), which also does not exist. Therefore, it resolves the unknown-type literal as text and uses the standard text = text operator. The only way to make the custom operator usable is to explicitly cast the literal:

    SELECT * FROM mytable WHERE val = text 'foo';

This query immediately finds the mytext = text operator using the exact match rules. If the resolution process reaches the best-match rules, these rules actively disfavor operators that are defined on domain types. Otherwise, such an operator would frequently cause ambiguous-operator failures. This is because the conversion rules always treat a domain as interchangeable with its base type, which makes the domain operator appear viable in any context where a similarly named base-type operator exists.

^[1] This risk does not arise with unqualified names, because including a schema that allows untrusted users to create objects in the search path is not a secure usage pattern.