UNION and CASE Constructs

PostgreSQL 7.3.4 Documentation
Prev | Chapter 7. Type Conversion | Next

# 7.5. UNION and CASE 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. A CASE construct also uses the identical algorithm to match up its component expressions and select a result data type.

UNION and CASE Type Resolution

1. 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.

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 coerced to it.

4. Coerce all inputs to the selected type.

## Examples

Example 7-7. Underspecified Types in a Union

    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.

Example 7-8. Type Conversion in a Simple Union

    tgl=> 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 7-9. Type Conversion in a Transposed Union

    tgl=> SELECT 1 AS "Real"
    tgl-> 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
Query Targets | Up | Indexes