[Wellington-pm] numeric types

Daniel Pittman daniel at rimspace.net
Sun Oct 26 21:26:46 PDT 2008


Richard Hector <richard at walnut.gen.nz> writes:

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

It doesn't matter what it does inside, but it has the correct property
of being an *exact* numeric type.

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

Perl?  Use an exact numeric type, and do all your math with that.
Seriously, don't screw around with (inexact) floating point numbers and
money, because it makes baby accountants cry.

Specifically, see bigrat(3perl) for the easy path, or Math::BigRat for
the library that uses if you want to do it the hard way.[1]

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

You can use Data::Dumper, or one of the non-bundled methods, to probe
what data type it is -- but, generally, it is fairly mutable.

Unfortunately DBD::Pg doesn't document handling of NUMERIC columns,
though it does note that it supports them for bind parameters, which is
a good start.

I strongly suspect it will return them as a string value, but don't have
time to test myself.  Perhaps you could, and let us know?

> How do I know what's happening, and how do I ensure that I'm getting
> what I want?

Hard work and enthusiasm.  You can probably derive appropriate black-box
testing from this: http://speleotrove.com/decimal/decifaq1.html#inexact

Otherwise, code inspection and care.  Check that DBD::Pg gives back
something you can use as an exact value, then make sure your ORM or
wrappers turn it into the right value. :)

'use bigrat' will be a big help in your code, though.

Regards,
        Daniel

Footnotes: 
[1]  ...or you only need it for a little bit of code. :)



More information about the Wellington-pm mailing list