Re: Rounding to even for numeric data type - Mailing list pgsql-hackers

On Sun, Mar 29, 2015 at 9:21 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:
> On 29/03/15 13:07, David G. Johnston wrote:
>>
>> On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier
>> <michael.paquier@gmail.com <mailto:michael.paquier@gmail.com>>wrote:
>>
>>     On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>>     <GavinFlower@archidevsys.co.nz
>>     <mailto:GavinFlower@archidevsys.co.nz>> wrote:
>>     > On 28/03/15 21:58, Dean Rasheed wrote:
>>     > [...]
>>     >>
>>     >>
>>     >> Andrew mentioned that there have been complaints from people doing
>>     >> calculations with monetary data that we don't implement
>>     >> round-to-nearest-even (Banker's) rounding. It's actually the
>>     case that
>>     >> various different financial calculations demand different specific
>>     >> rounding modes, so it wouldn't be enough to simply change the
>>     default
>>     >> - we would have to provide a choice of modes.
>>     >
>>     > [...]
>>     >
>>     > Could the 2 current round functions have cousins that included
>>     an extra char
>>     > parameter (or string), that indicated the type of rounding?
>>     >
>>     > So we don't end up with an explosion of rounding functions, yet
>>     could cope
>>     > with a limited set of additional rounding modes initially, and
>>     possibly
>>     > others in the future.
>>
>>     Instead of extending round, isn't what we are looking at here a new
>>     data type? I have doubts that we only want to have a way to switch
>>     round() between different modes. Hence, what we could do is:
>>     1) Mention in the docs that numeric does round-half-away-from-zero
>>     2) Add regression tests for numeric(n,m) and round(numeric)
>>     3) Add a TODO item for something like numeric2, doing rounding-at-even
>>     (this could be an extension as well), but with the number of
>>     duplication that it may have with numeric, an in-core type would make
>>     sense, to facilitate things exposing some of structures key structures
>>     would help.
>>
>>
>> So, create a numeric type for each possible rounding mode? That implies at
>> least two types, round-half-even and round-half-away-from-zero, with
>> suitable abbreviations: numeric_rhe, numeric_raz.

The existing numeric now does half-up rounding.

>> If the goal is to make plain numeric IEEE standard conforming then giving
>> the user a way to switch all existing numeric types to numeric_raz would be
>> nice.
>>
>> Implicit casts between each of the various numeric types would be needed
>> and understandable.

That's exactly the thing I think would be helpful.

>> I'm pondering calling them numeric_eng and numeric_bus (for engineering
>> and business respectively)...
>
> In Java, there are 8 rounding modes specified:
>
> https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html
> Some of these may be relevant to pg.

That's interesting. I didn't recall those details.
Regards,
-- 
Michael