PostgreSQL 8.2.6 Documentation
Prev Fast Backward Chapter 10. Type Conversion Fast Forward Next

10.5. UNION , CASE , and Related Constructs

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 CASE , ARRAY , VALUES , GREATEST and LEAST constructs use the identical algorithm to match up their component expressions and select a result data type.

Type Resolution for UNION , CASE , and Related Constructs

  1. If all inputs are of type unknown , resolve as type text (the preferred type of the string category). Otherwise, ignore the unknown inputs while choosing the result type.

  2. If the non-unknown inputs are not all of the same type category, fail.

  3. Choose the first non-unknown input type which is a preferred type in that category or allows all the non-unknown inputs to be implicitly converted to it.

  4. Convert all inputs to the selected type.

Some examples follow.

Example 10-7. 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 as type text .

Example 10-8. 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.

Example 10-9. 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 , but integer can be implicitly cast to real , the union result type is resolved as real .

Prev Home Next
Value Storage Up Indexes