Re: Money casting too liberal?

On 27/3/13 at 9:12 AM, Steve Crawford wrote:

>In contrast to certain other open-source databases, PostgreSQL leans
>toward protecting data from surprises ...
>
And long may this continue.

>But it appears that the philosophy does not extend to the "money"
>type. ...
>
>select ',123,456,,7,8.1,0,9'::money;
>money
>----------------
>$12,345,678.11
>
In general terms I would hate for such probable garbage to
appear as "legitimate" data in the dB.

>Somewhat more worrisome is the fact that it automatically rounds input (away from zero) to fit.
>
>select '123.456789'::money;
>money
>---------
>$123.46
>
>select '$-123.456789'::money;
>money
>----------
>-$123.46
>
>Thoughts? Is this the "no surprises" way that money input should behave?
>
I would defer to a CPA on the correct conventions for rounding.
However I have a vague notion there are circumstances when
rounding is always up, always down and (only sometimes) to the
nearest. If the money type is meant to be serious then these
conventions need to be followed/settable on a column by column
basis. And money is done in whole dollars, thousands of dollars,
and fractional cents according to the situation, i.e., not just
two decimal places... another setting.

Personally I have ignored the money type in favour of numeric.
Money seemed to do too much behind the scenes for my taste, but,
that's me being lazy as well, I haven't spend much time trying
to understand its features.

Regards
Gavan Schneider