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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
