[Wellington-pm] numeric types

[Andrew McMillan]

On Mon, 2008-10-27 at 16:38 +1300, Richard Hector wrote:
> Hi all,
> 
> I'm having a play with a database that involves money.
> 
> It seems that the proper thing to do is to use SQL's 'numeric' type,
> which from the Postgres docs will preserve whatever numbers I give it
> perfectly - it looks like it will be saved in BCD or similar to achieve
> that.
> 
> However, I'm not sure that's so useful if whenever I transfer between a
> Perl program and my DB, I'm converting it to a floating point number.
> 
> I'm not even clear that that's what will happen; it seems difficult to
> work out how Perl has actually stored a given value; perhaps DBI returns
> it as a string, which I can match and stick into a more complex data
> structure that preserves the components?
> 
> How do I know what's happening, and how do I ensure that I'm getting
> what I want?

The underlying reason why numeric types are a Good Idea for accounting
applications is that when millions of dollars are involved, floating
point numbers can have rounding errors around the cents.

There are other ways to deal with this, too.  If you can reasonably
expect that the amounts involved will never be larger than $20 million,
then you can of course use integer numbers of cents.

In reality a double-precision floating point can accurately represent
much larger numbers than that before rounding errors will appear at the
cents level.

So, if (e.g.) you are calculating the interest on something, in perl,
and then rounding to an exact monetary amount (i.e. no partial cents)
before storing that in the database, your decision is extremely unlikely
to matter in the real world.

If you're working on something for banks to calculate the amount of
filthy lucre they want the taxpayer to front up for, so the shareholders
don't go broke, then you might want to consider using real numeric types
in your perl program, but it should be fine without them even then.

The important point is to eliminate the possibility of errors through
successive rounding by always explicitly rounding at (accounting)
transaction boundaries, which is normal accounting practice.  Your bank
balance does not, and will not ever, hold fractions of cents.

Cheers,
					Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
                    You too can wear a nose mitten.
------------------------------------------------------------------------