postgres=# select (101::integer)/(2::integer);
?column?
----------
50
postgres=# select (1.01::numeric)/(2::numeric);
?column?
------------------------
0.50500000000000000000
Rounding errors are something you will need to deal with whether you use
INTEGER or NUMERIC fields. You will need to determine what the business logic
requirements are for the math. That is, what do your clients expect to happen
to fractional units of money? When during manual math operations are dollar
values rounded? Make your application work the way your client expects, not
the other way around.
I would use NUMERIC since it represents your data most correctly. Using
INTEGER for money invariably involves lots of excessive and possibly confusing
math with powers of 10. It's very easy to randomly be off by an order of
magnitude. With money, that's *bad*. INTEGER math also forces you to always
silently truncate fractional cents. That may not be what you want.
--
Brandon Aiken
CS/IT Systems Engineer
________________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Yonatan Ben-Nes
Sent: Monday, December 11, 2006 10:51 AM
To: [email protected]
Subject: [GENERAL] Numeric or Integer for monetary values?
Hi all,
I need to decide which data type should I make for monetary values, shall I use
Numeric data type to hold values like "9.52" or is it better to keep it as an
integer with value in cents like "952"?
I know that at the manual it's written about the Numeric data type that "It is
especially recommended for storing monetary amounts and other quantities where
exactness is required.", but I'm wondering what will happen at cases when I got
$1.01 to divide between 2 entities at 50% each, if both will get 51 cents or 50
cents it will be a mistake.
The calculation procedure will probably be made with PL/pgSQL, actually maybe
it doesn't even matter what the data type is (Integer/Numeric) as long as I
make enough validations for the result?
Cheers!
Ben-Nes Yonatan
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
