Re: simple division - Mailing list pgsql-general

I take the point that two decades of backward compatibility should and will win. That said,  it's an easy enough thing
toright the balance for novices and put in a really obvious place in the documentation what you should do if you want
todivide two integers and get the results with the number of decimals of your choice. I made one suggestion how this
couldbe 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
youwant 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@sss.pgh.pa.us> wrote:

    Geoff Winkless <pgsqladmin@geoff.dj> 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