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.[1]

3. If all inputs are of typeunknown, resolve as typetext (the preferred type of the string category). Otherwise,unknown inputs are ignored.

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.

Example 10-9. Type Resolution with Underspecified Types in a Union

SELECT text 'a' AS "text" UNION SELECT 'b'; text------ a 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; numeric---------       1     1.2(2 rows)

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

Example 10-11. Type Resolution in a Transposed Union

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real------    1  2.2(2 rows)

Here, since typereal cannot be implicitly cast tointeger, butinteger can be implicitly cast toreal, the union result type is resolved asreal.