November 21, 2024: PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released!
Supported Versions: Current (17) / 16 / 15 / 14 / 13
Development Versions: devel
Unsupported versions: 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.
PostgreSQL 7.3.21 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