I take the point that two decades of backward compatibility should and will win. That said, it's an easy enough thing to right the balance for novices and put in a really obvious place in the documentation what you should do if you want to divide two integers and get the results with the number of decimals of your choice. I made one suggestion how this could be done. A better way might be a short paragraph like
A note on division: if you divide two constants or variables defined as integers, the default will be an integer. If you want the result with decimals, add "::numeric". If you want to limit the decimals, use the round() function:
Select 10/3: 3
Select 10/3::numeric 3.33333
Round(select 10/3::numeric, 3) 3.333
For more detail see the sections on ...
`
On 12/5/18, 9:23 AM, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote:
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
