Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Hmm, I think (but am not sure) that the spec bit
> in SQL92 that addresses this is 9.3
> Set operation result data types based on the
> text in 7.10 query expression. It seems
> to say to me that should always be an
> approximate numeric (if 1.0 is an approximate
> numeric). Am I reading that right?
Yeah, the existing algorithm for determining CASE/UNION result datatype
does not have any smarts about preferring numeric over integer, which is
what's missing to handle this case per-spec.
There has been some speculation about junking the existing code (which
is mostly driven by a hardwired notion of "preferred types") in favor of
something driven by the contents of pg_cast. (At least I recall a
message or two about it, but I can't find it in the archives at the
moment.)
Nobody's made a specific proposal though --- and I'm more than a little
bit worried about the possible speed penalty of turning what's presently
a simple C switch-statement into a bunch of catalog lookups.
regards, tom lane