Re: rounding problems - Mailing list pgsql-general

Justin wrote:

> WE have several columns in table defined with numeric (20,10) thats is
> just insanity.

Not necessarily. I have a few places where a monetary value is
mulitiplied by a ratio quantity. For some of the historical data
imported from another system the ratio can be irrational or at least not
representable in any small precision value.

I ended up needing a precision of 8 numeric digits to acceptably
represent these ratios, resulting in a numeric(16,8) type to permit
ratio values up to 99999999.99999999 . I probably could've got away with
numeric(13,8) or even numeric(12,8) but as space and performance aren't
utterly critical it didn't seem to be worth the risk of hitting limits
and overflows later. As it is I'm tempted to go to 10 digits of
precision, as there's still a 3 cent difference between the totals from
the old system and the same data imported into the new system.

You'll encounter similar situations in your materials consumption
tracking (as you detailed below) and other places. So don't discount the
 use of high precision numeric values just yet.

Personally I'd be tempted to use a `double precision' (float8) for
things like materials consumption. Materials consumed in huge quantities
will have lower price rates, and materials consumed in tiny quantities
will often be priced higher. With wally-numbers: You're not going to
care about the 0.0003 kg of steel consumed at a price of $0.00001 , but
the same amount of something valuable might have a detectable (if still
sub-cent) value. Floating point numbers are IMO better for that than BCD
numeric. However, since the float will just get converted to numeric
during multiplication with a numeric price-per-mass ratio it may well
not be worth worrying about it.

There's a use for that numeric(20,10).


>   Unless your doing scientific calculations which we do,
> do.   Having that many decimal points for an accounting package is just
> nonsense and then its rounded to 4 or 6 in Inventory  and Wip tables
> then 2 when the numbers finally hit the GL tables. Who ever laid
> these tables out has never had to try and get numbers to balance and
> agree across tables :-( .   Every time i dig a little deeper i keep
> finding stupid things like this.

It sounds like you might have quite a bit of compounded rounding error
from the successive stages of rounding as data moves through the system.
Maybe you're rounding too aggressively?

I like to store a bit more precision than I have to, unless there's a
business rule that requires rounding to a particular precision. For
example, if your invoice items are rounded to whole cents you'd probably
round the calculated invoice item price when inserting into an invoice
item table.

Of course, that means that
   sum(calculation of invoice item price)
   <>
   sum(rounded price of invoice items)
because of rounding. That's fine; you can't balance the two things
exactly because they're actually subtly different things. If you're
using an appropriate rounding method for financial data, like
round-to-even, you'll only ever get a couple of cents difference and
that should be expected and ignored.

> Some people may think i'm crazy trying to track this down but when
> you're only consume 0.003186 lbs of a metal per part that cost 22.7868
> per  lb and the work order calls fro 1148 parts.  how the machine rounds
> becomes a big problem   (.00318611*1148) = 3.65765 lbs consumed *
> 22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored
> as $83.35

Thinking about correct rounding and precision is very important, and far
from crazy.

> The question quickly becomes what number is the correct number.

Sometimes the answer is "both of them" - even though they are different.
See the example above with rounded invoice items.

--
Craig Ringer