Re: Money casting too liberal?

On Thu, 28 Mar 2013 23:43:23 +1100
Gavan Schneider <pg-gts(at)snkmail(dot)com> wrote:
> >But it appears that the philosophy does not extend to the "money"
> >type. ...

As the original author of the money type I guess I should weigh in.

> >select ',123,456,,7,8.1,0,9'::money;
> >money
> >----------------
> >$12,345,678.11

It certainly doesn't accept that by design. I just never thought about
such input. If you put garbage in anything can happen including
acceptance. If this is an issue I guess we need to look for such things
and reject it. Just a SMOP.

> 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

Possible. Generally I handle these issues in code because it is
sometimes hard to nail down exact requirements that fit all. I also
tend to use money only in situations where the exact dollars and cents
is already known or is dealt with in code.

> 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.

I would like to see the type handle other situations such as foreign
(to me) currency, etc. I suppose a positional parameter and a currency
string setting would handle most of those issues. Technically, the
money type is a cents type. Everything is stored as the number of
cents. Formatting it as dollars and cents is a convenience added by
the I/O functions.

> Personally I have ignored the money type in favour of numeric.

Even as the author I sometimes go with numeric but there is a place for
the type. If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost. The big win that money brings is that
everything is stored as an int. That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc. The machine can generally work on the data as
it comes out of the DB.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy(at)Vex(dot)Net, VOIP: sip:darcy(at)Vex(dot)Net