CASE, and Related Constructs
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
EXCEPT constructs resolve dissimilar
types in the same way as
LEAST constructs use the identical algorithm to
match up their component expressions and select a result data
Type Resolution for
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
resolve as type
text (the preferred type
of the string category). Otherwise,
unknown inputs are ignored for the purposes of the
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.10. 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
will be resolved to type
Example 10.11. Type Resolution in a Simple Union
SELECT 1.2 AS "numeric" UNION SELECT 1; numeric --------- 1 1.2 (2 rows)
1.2 is of type
numeric, and the
1 can be
cast implicitly to
numeric, so that type
Example 10.12. 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 type
real cannot be
implicitly cast to
integer can be implicitly cast to
real, the union result type is resolved
Example 10.13. Type Resolution in a Nested Union
SELECT NULL UNION SELECT NULL UNION SELECT 1; ERROR: UNION types text and integer cannot be matched
This failure occurs because PostgreSQL treats multiple
UNIONs as a nest of pairwise operations; that is,
this input is the same as
(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
UNION is resolved as
text, according to the
rules given above. Then the outer
UNION has inputs of types
to the observed error. The problem can be fixed by ensuring that
UNION has at least one
input of the desired result type.
EXCEPT operations are likewise resolved pairwise.
However, the other constructs described in this section consider
all of their inputs in one resolution step.
 Somewhat like the treatment of domain inputs
for operators and functions, this behavior allows a domain type to
be preserved through a
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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.