The UNION and CASE 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. CASE uses the identical algorithm to match up its result expressions.
UNION and CASE Type Resolution
If all inputs are of type unknown, resolve as type text (the preferred type for string category). Otherwise, ignore the unknown inputs while choosing the type.
If the non-unknown inputs are not all of the same type category, fail.
If one or more non-unknown inputs are of a preferred type in that category, resolve as that type.
Otherwise, resolve as the type of the first non-unknown input.
Coerce all inputs to the selected type.
tgl=> SELECT text 'a' AS "Text" UNION SELECT 'b'; Text ------ a b (2 rows)Here, the unknown-type literal 'b' will be resolved as type text.
tgl=> SELECT 1.2 AS "Double" UNION SELECT 1;
Double
--------
1
1.2
(2 rows)
Here the output type of the union is forced to match the type of the first/top clause in the union:
tgl=> SELECT 1 AS "All integers"
tgl-> UNION SELECT CAST('2.2' AS REAL);
All integers
--------------
1
2
(2 rows)
Since REAL is not a preferred type, the parser sees no reason to select it over INTEGER (which is what the 1 is), and instead falls back on the use-the-first-alternative rule. This example demonstrates that the preferred-type mechanism doesn't encode as much information as we'd like. Future versions of Postgres may support a more general notion of type preferences.