Thread: 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
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
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 determinewhat the business logic requirements are for the math. That is, what do your clients expect to happen to fractionalunits of money? When during manual math operations are dollar values rounded? Make your application work theway 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 excessiveand possibly confusing math with powers of 10. It's very easy to randomly be off by an order of magnitude. Withmoney, that's *bad*. INTEGER math also forces you to always silently truncate fractional cents. That may not be whatyou want. -- Brandon Aiken CS/IT Systems Engineer ________________________________________ From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yonatan Ben-Nes Sent: Monday, December 11, 2006 10:51 AM To: pgsql-general@postgresql.org 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 monetaryamounts 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
"Yonatan Ben-Nes" <yonatan@epoch.co.il> writes: > 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? You'll have the problem with any of these data type unless you have enough precision stored. To make it work you shouldn't store cents but smaller fractions of it. I use numeric with at least 4 digits after the comma. When I need some input or need to show the user some information I show only two digits as it is usual. Sometimes I allow them to work with 4 digits as well. Here in Brazil fuel is sold using 3 decimal places (e.g. 2.597 reais per liter of gasoline) so I'd need at least that precision anywhere I was going to deal with fuel costs. With regards to inexact fractions, what happens depends on the law of the country. The law here determinates that the value should be truncated to two decimal places. If I bought one liter of gasoline I should pay 2.59 reais and not 2.60. There are places where you could have rounding (e.g. calculations inside a company) instead of truncation... Dealing with money is a really complex subject and requires a lot of planning. My recommendation is to get two decimal places more than needed to avoid rounding issues (and if you need truncation, the truncate only at the very end where you'll have minimum loss). -- Jorge Godoy <jgodoy@gmail.com>