10.5. UNION、CASE等相關操作1

SQLUNIONconstructs must match up possibly dissimilar types to become a single result set. The resolution algorithm is applied separately to each output column of a union query. TheINTERSECTandEXCEPTconstructs resolve dissimilar types in the same way asUNION. TheCASE,ARRAY,VALUES,GREATESTandLEASTconstructs use the identical algorithm to match up their component expressions and select a result data type.

Type Resolution forUNION,CASE, and Related Constructs

  1. If all inputs are of the same type, and it is notunknown, resolve as that type.

  2. If any input is of a domain type, treat it as being of the domain's base type for all subsequent steps.[9]

  3. If all inputs are of typeunknown, resolve as typetext(the preferred type of the string category). Otherwise,unknowninputs are ignored for the purposes of the remaining rules.

  4. If the non-unknown inputs are not all of the same type category, fail.

  5. Choose the first non-unknown input type which is a preferred type in that category, if there is one.

  6. Otherwise, choose the last non-unknown input type that allows all the preceding non-unknown inputs to be implicitly converted to it. (There always is such a type, since at least the first type in the list must satisfy this condition.)

  7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type.

Some examples follow.

Example 10.9. Type Resolution with Underspecified Types in a Union

SELECT text 'a' AS "text" UNION SELECT 'b';

(2 rows)

Here, the unknown-type literal'b'will be resolved to typetext.

Example 10.10. Type Resolution in a Simple Union

SELECT 1.2 AS "numeric" UNION SELECT 1;

(2 rows)

The literal1.2is of typenumeric, and theintegervalue1can be cast implicitly tonumeric, so that type is used.

Example 10.11. Type Resolution in a Transposed Union


(2 rows)

Here, since typerealcannot be implicitly cast tointeger, butintegercan be implicitly cast toreal, the union result type is resolved asreal.

[9]Somewhat like the treatment of domain inputs for operators and functions, this behavior allows a domain type to be preserved through aUNIONor similar construct, so long as the user is careful to ensure that all inputs are implicitly or explicitly of that exact type. Otherwise the domain's base type will be preferred.

1. PostgreSQL: Documentation: 10: 10.5. UNION, CASE, and Related Constructs

results matching ""

    No results matching ""