Re: Money casting too liberal?

On 30/03/13 08:36, Michael Nolan wrote:
> On 3/27/13, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
>
>
>> Somewhat more worrisome is the fact that it automatically rounds input
>> (away from zero) to fit.
>>
>> select '123.456789'::money;
>> money
>> ---------
>> $123.46
> So does casting to an integer:
>
> select 1.25::integer
> ;
> int4
> ----
> 1
>
> And then there's this:
>
> create table wkdata
> (numval numeric(5,2))
>
> CREATE TABLE
> Time: 6.761 ms
> nolan=> insert into wkdata
> nolan-> values (123.456789);
> INSERT 569625265 1
> Time: 4.063 ms
> nolan=> select * from wkdata;
> select * from wkdata;
> numval
> ------
> 123.46
>
> So rounding a money field doesn't seem inconsistent with other data types.
> --
> Mike Nolan
>
>
In New Zealand at one point we rounded to the nearst 5 cents now to 10
cents, probably in a few years we will round to the nearest 20c or
50c... Not sure how people, if they ever did, coped with printing
values before or after the change in the value to be rounded (say to the
nearest 5c then the next day to the nearest 10c)!

There are many rounding modes, from Java (Enum RoundingMode):

CEILING: Rounding mode to round towards positive infinity.

DOWN: Rounding mode to round towards zero.

FLOOR: Rounding mode to round towards negative infinity.

HALF_DOWN: Rounding mode to round towards "nearest neighbor" unless
both neighbors are equidistant, in which case round down.

HALF_EVEN: Rounding mode to round towards the "nearest neighbor"
unless both neighbors are equidistant, in which case, round towards the
even neighbor.

HALF_UP: Rounding mode to round towards "nearest neighbor" unless
both neighbors are equidistant, in which case round up.

UNNECESSARY: Rounding mode to assert that the requested operation has an
exact result, hence no rounding is necessary.

UP: Rounding mode to round away from zero.