SQL UNION constructs 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. The INTERSECT and
EXCEPT constructs resolve dissimilar
types in the same way as UNION. The
LEAST constructs use the identical algorithm to
match up their component expressions and select a result data
Type Resolution for UNION, CASE, and Related Constructs
If all inputs are of the same type, and it is not unknown, resolve as that type.
If any input is of a domain type, treat it as being of the domain's base type for all subsequent steps. 
If all inputs are of type unknown, resolve as type text (the preferred type of the string category). Otherwise, unknown inputs are ignored.
If the non-unknown inputs are not all of the same type category, fail.
Choose the first non-unknown input type which is a preferred type in that category, if there is one.
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.)
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'; text ------ a b (2 rows)
Here, the unknown-type literal 'b' will be resolved to type text.
Example 10-10. Type Resolution in a Simple Union
SELECT 1.2 AS "numeric" UNION SELECT 1; numeric --------- 1 1.2 (2 rows)
The literal 1.2 is of type numeric, and the integer value 1 can be cast implicitly to numeric, so that type is used.
Somewhat like the treatment of domain inputs for operators and functions, this behavior allows a domain type to be preserved through a UNION or 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.
Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.
Proceed to the comment form.