Geoff Winkless <pgsqladmin@xxxxxxxx> writes:
> IMO it's fundamentally broken that SQL doesn't cast the result of a
> divide into a numeric value - the potential for unexpected errors
> creeping into calculations is huge; however that's the standard and
> no-one's going to change it now.
> Having said that it's worth noting that those in the Other Place think
> that it's broken enough to go against the standard (they have a DIV b
> for integer divide and a/b for float).
Well, this isn't really blame-able on the SQL standard; it's a
Postgres-ism. What the spec says (in SQL99, 6.26 <numeric value
expression>) is
1) If the declared type of both operands of a dyadic arithmetic
operator is exact numeric, then the declared type of the
result is exact numeric, with precision and scale determined
as follows:
a) Let S1 and S2 be the scale of the first and second operands
respectively.
b) The precision of the result of addition and subtraction is
implementation-defined, and the scale is the maximum of S1
and S2.
c) The precision of the result of multiplication is
implementation-defined, and the scale is S1 + S2.
d) The precision and scale of the result of division is
implementation-defined.
2) If the declared type of either operand of a dyadic arithmetic
operator is approximate numeric, then the declared type of the
result is approximate numeric. The precision of the result is
implementation-defined.
Postgres' integer types map onto the standard as exact numerics with
scale 0. (The precision aspect is a bit squishy, since their maximum
values aren't powers of 10, but let's disregard that.) Postgres'
integer division operator meets the spec with the stipulation that
the "implementation-defined" scale of the result is 0. Other SQL
implementations can and do define that differently --- if they even
have an "integer" data type, which some do not.
Anyway, the bottom line here is that we're balancing surprise factor
for novices against twenty-plus years of backwards compatibility,
and the latter is going to win.
regards, tom lane
