<secondary>determination of result type</secondary>
</indexterm>

<indexterm zone="typeconv-union-case">
 <primary>GREATEST</primary>
 <secondary>determination of result type</secondary>
</indexterm>

<indexterm zone="typeconv-union-case">
 <primary>LEAST</primary>
 <secondary>determination of result type</secondary>
</indexterm>

<para>
SQL <literal>UNION</literal> 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
<literal>INTERSECT</literal> and <literal>EXCEPT</literal> constructs resolve
dissimilar types in the same way as <literal>UNION</literal>.
Some other constructs, including
<literal>CASE</literal>, <literal>ARRAY</literal>, <literal>VALUES</literal>,
and the <function>GREATEST</function> and <function>LEAST</function>
functions, use the identical
algorithm to match up their component expressions and select a result
data type.
</para>

<procedure>
<title>Type Resolution for <literal>UNION</literal>, <literal>CASE</literal>,
and Related Constructs</title>

<step performance="required">
<para>
If all inputs are of the same type, and it is not <type>unknown</type>,
resolve as that type.
</para>
</step>

<step performance="required">
<para>
If any input is of a domain type, treat it as being of the
domain's base type for all subsequent steps.
  <footnote>
   <para>
    Somewhat like the treatment of domain inputs for operators and
    functions, this behavior allows a domain type to be preserved through
    a <literal>UNION</literal> or 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 used.
   </para>
  </footnote>
</para>
</step>

<step performance="required">
<para>
If all inputs are of type <type>unknown</type>, resolve as type
<type>text</type> (the preferred type of the string category).
Otherwise, <type>unknown</type> inputs are ignored for the purposes
of the remaining rules.
</para>
</step>

<step performance="required">
<para>
If the non-unknown inputs are not all of the same type category, fail.
</para>
</step>

<step performance="required">
<para>
Select the first non-unknown input type as the candidate type,
then consider each other non-unknown input type, left to right.
  <footnote>
   <para>
    For historical reasons, <literal>CASE</literal> treats
    its <literal>ELSE</literal> clause (if any) as the <quote>first</quote>
    input, with the <literal>THEN</literal> clauses(s) considered after
    that.  In all other cases, <quote>left to right</quote> means the order
    in which the expressions appear in the query text.
   </para>
  </footnote>
If the candidate type can be implicitly converted to the other type,
but not vice-versa, select the other type as the new candidate type.
Then continue considering the remaining inputs.  If, at any stage of this
process, a preferred type is selected, stop considering additional
inputs.
</para>
</step>

<step performance="required">
<para>
Convert all inputs to the final candidate type.  Fail if there is not an
implicit conversion from a given input type to the candidate type.
</para>
</step>
</procedure>

<para>
Some examples follow.
</para>

<example>
<title>Type Resolution with Underspecified Types in a Union</title>

<para>
<screen>
SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)
</screen>
Here, the unknown-type literal <literal>'b'</literal> will be resolved to type <type>text</type>.