UNION, CASE, and Related Constructs

SQL UNION constructs match different data types and output a unified data type result set. Since all query results in a SELECT UNION statement must be displayed in a single column, the element types in each SELECT clause must match each other and be converted into a unified data type. Similarly, the result expression of a CASE construct must be converted to a unified type so that the entire case expression has a unified output type. The same requirement exists in the ARRAY construct and in the GREATEST and LEAST functions.

Resolution for UNION, CASE, and Related Constructs

If all inputs are of the same data type, excluding the unknown type (that is, when the input string literal's type is undeclared and initially treated as an unknown type), it resolves to the same data type as the input.
If all inputs are of the unknown type, they resolve to the text type (the preferred type within the string category). Otherwise, unknown inputs are ignored.
If the inputs do not belong to the same type category, the query fails (excluding the unknown type).
If the input types belong to the same type category, the preferred type of that category is selected (unless the union operation chooses the type of the first branch as the selected type).

In the pg_type system catalog, typcategory indicates the data type category, and typispreferred indicates whether the data type is the preferred one within its typcategory.
Convert all inputs to the selected type (retaining the original length for strings). If there is no implicit conversion from the given input to the selected type, the conversion fails.
If the input includes json, txid_snapshot, sys_refcursor, or geometric types, union operations cannot be performed.