"Jeff Wu" <jwu@atlassian.com> writes:
> The UNION construct (as noted on this page:
> http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
> cast unknown types to TEXT, however, if you try to do three or more UNIONs
> the order in which the UNIONs are executed will cause some columns to be
> cast to TEXT prematurely. The result is a type mismatch error.
Really the *right* fix for this would be to resolve the common type
just once across the whole nest of set operations. That wouldn't be
terribly difficult from a coding standpoint, I think. The reason we
haven't done it is that it looks like the SQL standard requires type
resolution for set-ops to happen one pair of input relations at a time.
See SQL:2008 7.13 <query expression>, in which everything that's said
about UNION/INTERSECT/EXCEPT is phrased in terms of exactly two input
subqueries; for instance INTERSECT's result type is defined in syntax
rule 18b as:
The declared type of the i-th column of TR is determined by
applying Subclause 9.3, "Result of data type combinations", to
the declared types of the i-th column of T1 and the i-th column
of T2.
If anyone can think of a way to read the spec to allow subclause 9.3 to
be applied to the whole set of columns at once, we could make this work
less surprisingly. Or maybe we could find out that some other products
do it like that despite what the spec says?
regards, tom lane