Re: Best data type to use for sales tax percent

On Fri, Oct 09, 2009 at 12:10:41AM -0700, Mike Christensen wrote:
> Wouldn't (4,3) let me store 0.000 through 9.999? Maybe I'm still not
> following what both numbers mean.

I think Rich was getting confused about how you wanted to represent your
percentages.

> I understand the point about states/counties with 3 decimal digits of
> sales tax, so I'd probably want to do (5,5) which should give me
> 0.00000 - 0.99999, and store 9.825% sales tax as .09825. I'm
> suggesting storing sales tax as a number between 0 and 1 so I can
> easily multiply it against a subtotal to get the tax amount, storing
> anything over 1.0 is unnecessary.

This is how I'd normally do it. Ratios for inside the code, just
"format" them as percentages when you want the user to see them.

> Also, if you just say "numeric" (without any numbers) then Postgres
> lets you store any number you wish and will never do any rounding of
> any sort, correct? If there a price you pay for this in terms of
> perf, bytes on disk, etc?

It's not possible to do division accurately (not sure about the caveats
in other operators). For example, 1/3 is represented as "0.33333" and
multiplying this by three again will give "0.99999". When people say
that numeric types are "exact" they're not giving you whole truth.

> Another idea is if I'm tying myself down to a certain level of decimal
> accuracy in the first place, why not just store everything as an Int2?
> 9.825% would be stored as 9825 and I'll divide everything by 100000
> when I calc sales tax. If I'm not mistaken, integral data types are
> faster for Postgres and less bytes on disk, right? BTW, I will never
> be doing any math using Postgres, it's just for pure storage..

Not sure what range of values you have to cover; you wouldn't be able to
do this with fixed width integer types:

select numeric '100' ^ 300;

Numeric types allow you to do the above, the flexibility of allowing the
representation of a number to get this wide that causes things to be
slower. It's not much slower though, I'd benchmark a test case that's
meaningful to you and then can you make a sensible decision.

--
Sam http://samason.me.uk/