Peter Eisentraut <peter_e@gmx.net> writes:
> The resolution to my problem with the select_common_type() error message
> turned out to be that this doesn't work:
> postgres=# select null union select null union select 1;
> ERROR: UNION types text and integer cannot be matched
Yeah, this has been noted before. The sticking point is that it's not
clear that resolving types across more than two branches at a time is
legal per SQL spec. The spec defines UNION as exactly two at a time,
ie the above is really(select null union select null) union select 1;
and there is not any language that would justify allowing the "1" to
determine the data type of the inner UNION. It would not be all that
important in a UNION ALL case, maybe, but for UNION the assigned data
type determines what values are considered duplicates, and thus can
have real impact on the results.
Maybe we should just ignore those qualms and do it anyway --- I must
admit that I'm hard-pressed to come up with a situation where anyone
would really want different datatypes used in the inner union than
the outer.
regards, tom lane