Re: Money casting too liberal? - Mailing list pgsql-general

On Thu, 28 Mar 2013 23:43:23 +1100
Gavan Schneider <pg-gts@snkmail.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@druid.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@Vex.Net, VOIP: sip:darcy@Vex.Net