Thread: union query

Just spent an hour getting nowhere, til I discovered this (description is a
varchar field):

    select description from address union select 'other';

fine but...

   select 'other' union select description from address;
   ERROR:  Unable to transform varchar to unknown
        Each UNION | EXCEPT | INTERSECT clause must have compatible target
types

Its not a problem, as I now I've worked it out I can do what I wanted (a
long nasty union to get around some outer join issues), but I just wondered
if the order of the selects in a union should matter?

tamsin

"Tamsin" <tg_mail@bryncadfan.co.uk> writes:
>    select 'other' union select description from address;
>    ERROR:  Unable to transform varchar to unknown
>         Each UNION | EXCEPT | INTERSECT clause must have compatible target
> types

The behavior in 7.0.* and before (as far back as I recall) has been that
the first select determines the output type of the union, so the above
fails, but reversing it works.  7.1 is a little smarter about
unknown-type literals, so it accepts both of your cases, but it will
still do the Wrong Thing for examples like select int4 union select int8.

> I just wondered
> if the order of the selects in a union should matter?

It shouldn't, really, but without a complete type promotion hierarchy
we have a hard time doing anything intelligent with arbitrary pairs of
types.

            regards, tom lane