PostgreSQL: Which Datatype should be used for Currency?
PostgreSQL: Which Datatype should be used for Currency?
Seems like Money type is discouraged as described here
Money
My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?
5 Answers
5
Numeric with forced 2 units precision. Never use float or float like datatype to represent currency because if you do, people are going to be unhappy when the financial report's bottom line figure is incorrect by + or - a few dollars.
The money type is just left in for historical reasons as far as I can tell.
That's not why you avoid floating point. Even Numeric will have rounding errors if you divide by anything that doesn't divide into a power of ten, no matter what precision you use. (Precision of 2 is a Bad Idea anyway... check the docs.)
– Doradus
Jan 9 at 12:05
Your source is in no way official. It dates to 2011 and I don't even recognize the authors. If the money type was "discouraged" PostgreSQL would say so in the manual - which it doesn't.
For a more official source, read this thread in pgsql-general (from just this week!), with statements from core developers including D'Arcy J.M. Cain (original author of the money type) and Tom Lane:
Basically, money has its (limited) uses. The advantage over numeric is performance.
money
numeric
decimal is just an alias for numeric in Postgres.
decimal
numeric
Related answer (and comments!) about improvements in recent releases:
Personally, I like to store currency as integer representing Cents. That's more efficient than any other of the mentioned options.
integer
There are several discussions on the mailing lists which do give the impression that the money type is at least not recommended, e.g.: here: postgresql.nabble.com/Money-type-todos-td1964190.html#a1964192 plus to be fair: the manual for version 8.2 did call it deprecated: postgresql.org/docs/8.2/static/datatype-money.html
– a_horse_with_no_name
Jan 13 '15 at 19:22
@a_horse_with_no_name: Your link is to a thread from 2007, which is also when 8.2 was the current version and the
money type was, in fact, deprecated. Issues have been fixed and the type has been added back in later versions. Personally I like to store currency as integer representing Cents.– Erwin Brandstetter
Mar 1 '15 at 17:49
money
integer
Erwin, you may be correct thinking from a database perspective alone. However, if you combine Postgresql + Java, it is NOT at all good (from my experience). Reading your comment, I used MONEY for most of my currency fields and now I get this Java exception : "SQLException occurred : org.postgresql.util.PSQLException: Bad value for type double : 2,500.00". I have googled and found no good solution, so I am into the boring task of changing all of them to NUMERIC or DECIMAL now!!!
– M-D
May 26 '15 at 16:11
@M-D: Sorry to hear that, but I obviously did not speak for Java (which I can't). The error message is odd. "double"? And the thousands-separator might be a problem, too. You might want to start a new question about that.
– Erwin Brandstetter
May 26 '15 at 17:24
@PirateApp: Yes, my personal favorite. You may have missed the last sentence of my answer, saying just that.
– Erwin Brandstetter
Mar 25 at 21:47
Your choices are:
integer
decimal(12,2)
float
Option 2 is the most common and easiest to work with. Make the precision (12 in my example, meaning 12 digits in all) as large or small as works best for you.
Note that if you are aggregating multiple transactions that were the result of a calculation (eg involving an exchange rate) into a single value that has business meaning, the precision should be higher to provide a accurate macro value; consider using something like decimal(18, 8) so the sum is accurate and the individual values can be rounded to cent precision for display.
decimal(18, 8)
If you are working with any kind of reverse tax calculation or foreign exchange, you need at least 4 decimal places, or you will lose data. So
numeric(15,4) or numeric(15,6) is a good idea.– Petrus Theron
Jan 23 '17 at 13:24
numeric(15,4)
numeric(15,6)
There is a 4th option - that is to use a String and use an equivalent non-lossy decimal type in the host language.
– ioquatix
Apr 19 '17 at 4:17
what about a scaled integer, surely storing 10000.045 wouldnt hurt if it was stored as 10000045 with a 1000x scaling factor?
– PirateApp
Mar 18 at 9:39
I keep all of my monetary fields as:
numeric(15,6)
numeric(15,6)
It seems excessive to have that many decimal places, but if there's even the slightest chance you will have to deal with multiple currencies you'll need that much precision for converting. No matter what I'm presenting a user, I always store to US Dollar. In that way I can readily convert to any other currency, given the conversion rate for the day involved.
If you never do anything but one currency, the worst thing here is that you wasted a bit of space to store some zeroes.
This has a risk of wrong results because of lack of truncation. If nonzero values unintentionally leak into the remaining decimal places, for example, a price field containing 0.333333 dollars, then you can have a situation where the system shows a result of someone buying 3 items at $0.33 each, totaling up to $1.00 instead of $0.99.
– Peteris
Jul 19 '16 at 11:15
Perteris, so what do you suggest instead? No matter how much precision you throw at this rounding can be an issue. I simply haven't found a better way, even if this one isn't ideal.
– Michael Collette
Jul 25 '16 at 13:23
Fixed point and truncate wherever appropriate. As soon as you reach a "storable" money value e.g. a price offered to a customer, it should be in appropriate metrics, which in most cases would be in whole cents in standard retail environment. If you have different business needs (e.g. price of high-volume goods per unit) there might be a different setting of accuracy, but you must treat the presentation together with storage - if you display the money number with x decimals (or vice versa, e.g. in whole thousands) then you must also store it with that accuracy, no less but also no more.
– Peteris
Jul 25 '16 at 14:08
For many retail related sites that may work. Main project I work with may have one party needing to see the same cost in one currency, with a client in another currency, for a supplier in yet a 3rd.
– Michael Collette
Jul 25 '16 at 16:30
After extensive experience in many applications, what I recommend is bigint, which is a 64-bit integer storing values in micro-dollars (or similar major currency).
bigint
Micro = 1 millionth, so 1 micro-dollar is $1 / 1,000,000. This provides a very fine granularity that can handle pretty much any fraction of a cent. This works especially well for small per-unit pricing like ad campaign impressions or API charges.
64 bit integers are small, easy to store, easy to handle, compatible with everything, and avoid data loss in calculations. You can apply rounding at the final output level (like creating an invoice) and maintain accuracy all the way through.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
If you have read the whole thread, Numeric is the way to go.
– razpeitia
Mar 31 '13 at 5:10