"Josh Berkus" <josh@agliodbs.com> writes:
> And, just to throw a banana peel onto the sidewalk of argument, there is
> even a reason to keep things the way they are. [ ... ]
> Thus there's a good reason for the database to be indecisive about
> float/numeric decisions, and good reason for you to explicitly CAST.
Indeed. However, the SQL92 spec has its mind made up: section 6.12 says
1) If the data type of both operands of a dyadic arithmetic opera- tor is exact numeric, then the
datatype of the result is exact numeric, [ ... ]
2) If the data type of either operand of a dyadic arithmetic op- erator is approximate numeric, then
thedata type of the re- sult is approximate numeric.
So it's clear that for spec conformance we should cast numeric to float8
and do the multiply in float math. (Never mind that this could result
in loss of precision or even overflow :-( since PG's numerics actually
have a wider range of values than float8.)
A closely related issue is that SQL92 expects a literal like "1.234"
to be considered exact numeric, not approximate, so our initial typing
of such literals as float8 is wrong too. We'll have to fix literal
processing as well as the float8-vs-numeric-preference issue before we'll
have spec-compliant processing of expressions like "numericvar * 1.234".
(And yes, there's been lots and lots of discussion about how to do that,
too...)
regards, tom lane