221

Seems like Money type is discouraged as described here.

My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?

user4157124
  • 2,809
  • 13
  • 27
  • 42
daydreamer
  • 87,243
  • 191
  • 450
  • 722
  • 13
    If you have read the whole thread, Numeric is the way to go. – razpeitia Mar 31 '13 at 05:10
  • For anyone working with multiple currencies and caring about storing currency codes in addition to the amounts, you may want to see [Currency modeling in database](https://stackoverflow.com/questions/1844221/currency-modeling-in-database) (SO) and [ISO 4217](https://en.wikipedia.org/wiki/ISO_4217) (Wikipedia). The short answer is that you'll need two columns. – Fabien Snauwaert Feb 08 '19 at 19:03
  • 1
    [Don't use money](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money) –  Jun 23 '20 at 11:07

8 Answers8

178

Your source is in no way official. It dates to 2011 and I don't even recognize the authors. If the money type was officially "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:

Related answer (and comments!) about improvements in recent releases:

Basically, money has its (very limited) uses. The Postgres Wiki suggests to largely avoid it, except for those narrowly defined cases. The advantage over numeric is performance.

decimal is just an alias for numeric in Postgres, and widely used for monetary data, being an "arbitrary precision" type. The manual:

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required.

Personally, I like to store currency as integer representing Cents if fractional Cents never occur (basically where money makes sense). That's more efficient than any other of the mentioned options.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 5
    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: http://postgresql.nabble.com/Money-type-todos-td1964190.html#a1964192 plus to be fair: the manual for version 8.2 **did** call it deprecated: http://www.postgresql.org/docs/8.2/static/datatype-money.html –  Jan 13 '15 at 19:22
  • 14
    @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 01 '15 at 17:49
  • 2
    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
  • 1
    @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
  • 1
    @Erwin, Its okay! I did some googling and found that, its better to avoid MONEY type and use NUMERIC or DECIMAL instead. So, I did not want to start a thread. – M-D May 27 '15 at 18:04
  • 3
    If you store as an integral number of cents, what do you do about prices with fractional cents, like gas prices? – Kevin Aug 21 '17 at 14:26
  • 3
    @Kevin: In many use cases the column only represents actual monetary values - where the minimum possible unit is cents. If you need fractional cents, `integer` cannot be used, look to `numeric`. Or you shift 5 decimal positions instead of just 2, representing milli-cents - or the actual scale you need. Whatever covers your use case. – Erwin Brandstetter Aug 21 '17 at 14:48
  • what about a scaled integer? 1000.45 numeric(6,2) (6 bytes) stored as 100045 (4 byte) – PirateApp Mar 18 '18 at 09:40
  • 2
    @PirateApp: Yes, my personal favorite. You may have missed the last sentence of my answer, saying just that. – Erwin Brandstetter Mar 25 '18 at 21:47
  • Note that the Postgres wiki discourages the use of money: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money –  Jun 23 '20 at 11:08
  • @a_horse_with_no_name: I agree with the Wiki, situations where money actually makes sense are few and far between. I added a link to the Wiki and a quote from the manual to clarify. Personally, I have never had such a use case. And I'd probably still go for `integer` then, for it being the much more common type with no surprises. Still, the type `money` is not officially discouraged, like `timetz` is for instance. – Erwin Brandstetter Jun 24 '20 at 00:49
125

Your choices are:

  1. bigint : store the amount in cents. This is what EFTPOS transactions use.
  2. decimal(12,2) : store the amount with exactly two decimal places. This what most general ledger software uses.
  3. float : terrible idea - inadequate accuracy. This is what naive developers use.

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 19
    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
  • 5
    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 04:17
  • 4
    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 '18 at 09:39
123

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.

Take this as an example: 1 Iranian Rial equals 0.000030 United States Dollars. If you use fewer than 5 fractional digits then 1 IRR will be rounded to 0 USD after conversion. I know we're splitting rials here, but I think that when dealing with money you can never be too safe.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
Chris Farmiloe
  • 13,935
  • 5
  • 48
  • 57
  • 14
    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 09 '18 at 12:05
  • 11
    If you want to support arbitrary currencies, never make a scale equal to 2 (in postgresql terms, precision is a number of all digits, e.g. in 121.121 it's equal to 6). There are currencies, such as Bahrain Dinar, where 1000 sub-units equal one unit, and there are currencies which don't have sub-units at all. – Nikolay Arhipov Aug 24 '18 at 05:49
  • @NikolayArhipov good point, so the max is actually `scale - precision` – Konrad Sep 11 '18 at 09:20
  • 2
    `numeric(3,2)` will be able to store max `9.99` `3-2 = 1` – Konrad Sep 11 '18 at 09:21
  • 35
    Do not do this! Unless you plan on multiplying by 100 before loading any values into other languages and then doing math with integers - you will end up with wrong results. Store things in cents (smallest currency unit you're dealing with) and save yourself some hassle. Very bad answer in many cases. – Avamander Jun 02 '19 at 22:04
  • 1
    This works ONLY if you are not interested in any value after the second decimal number and wants your DB to perform a rounding to the second decimal. If you have a `numeric(6,2)` and you try to insert a value like `105.666` you'll get stored a `105.67`, and this might not be what you want. Besides, with a similar configuration you can store values up to `9999.99`. – kekko12 Jul 22 '19 at 14:42
  • 2
    To add to what @NikolayArhipov said, take this as an example: 1 Iranian Rial equals 0.000030 United States Dollars. If you use fewer than 5 fractional digits then 1 IRR will be rounded to 0 USD after conversion. I know we're splitting rials here, but I think that when dealing with money you can never be too safe. I'd use 6 fractional digits. – Juliusz Gonera Feb 02 '20 at 02:51
62

Use a 64-bit integer stored as bigint

Store in the small currency unit (cents) or use a big multiplier to create larger integers if cents are not granular enough. I recommend something like micro-dollars where dollars are divided by 1 million.

For example: $5,123.56 can be stored as 5123560000 microdollars.

  • Simple to use and compatible with every language.
  • Enough precision to handle fractions of a cent.
  • Works for very small per-unit pricing (like ad impressions or API charges).
  • Smaller data size for storage than strings or numerics.
  • Easy to maintain accuracy through calculations and apply rounding at the final output.
Mani Gandham
  • 7,688
  • 1
  • 51
  • 60
  • 4
    This is the most correct answer and any reasonable software deals with the smallest currency unit in hand. Doing all math on integers means you do not have to deal with any language-specific float mangling. – Avamander Jun 02 '19 at 22:02
  • 2
    Why this over `numeric(15,6)` suggested in another answer? – Juliusz Gonera Feb 01 '20 at 23:50
  • 1
    @JuliuszGonera For the reasons listed in the answer. Integers are smaller and supported everywhere, and avoid all of the math truncation problems. It's basically using numeric but shifting the decimals so you have a whole number that's much more compatible. – Mani Gandham Feb 02 '20 at 01:06
  • 4
    Ah, right, I missed the part about storage. Thanks! Regarding "Simple to use and compatible with every language" unfortunately JavaScript supports integers up to 9007199254740991 which is over 1000x smaller than max value of `bigint`. There is https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/BigInt but it comes with limited support (for now) and caveats (e.g. you can't multiply it by a float easily when doing currency conversion). Given that the max you can store in a JS integer using micro-dollars is $9 billion that's probably still good for most cases. – Juliusz Gonera Feb 02 '20 at 02:47
  • 6
    I dug a little bit deeper. If you ever want to support cryptocurrencies, then `bigint` may also not be enough. Bitcoin offers precision of 8 fractional points, Monero 12, Ethereum 18. In fact [Ethereum VM internally uses `uint256`](https://github.com/ethereum/wiki/wiki/Design-Rationale) which would be 4 `bigint`s (unsigned at that so you'd probably just use `numeric(72,18)`). That being said, for some applications, it might not be necessary to store such precise values (e.g. if you don't do any math on them) so I'll stick to `bigint` for now. Worst case that's what DB migrations are for :) – Juliusz Gonera Feb 02 '20 at 16:53
35

I keep all of my monetary fields as:

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.

Michael Collette
  • 359
  • 4
  • 2
  • 8
    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
  • 1
    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
  • 3
    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
  • 5
    Peteris, this is a crap-in, crap-out scenario. If you allow a price field to have a value of 0.333333 when it should have 0.33, that is an application error. Storing more accuracy is very useful for currency-conversion, as mentioned. – mikl Nov 17 '20 at 23:37
22

Use BigInt to store currency as a positive integer representing the monetary value in the smallest currency unit (e.g., 100 cents to store $1.00 or 100 to store ¥100 (Japanese yen, a zero-decimal currency). This is what Stripe does--one the most important financial service companies for global ecommerce.

Source: see "Zero-decimal currencies" at https://stripe.com/docs/currencies

Eventually this will be the top answer...

ChatGPT
  • 5,334
  • 12
  • 50
  • 69
  • 6
    _This is what Stripe does_ Do you have a source for this statement? – Ivaylo Toskov Feb 17 '21 at 10:09
  • @IvayloToskov "Zero-decimal currencies All API requests expect amounts to be provided in a currency’s smallest unit. For example, to charge 10 USD, provide an amount value of 1000 (i.e., 1000 cents). For zero-decimal currencies, still provide amounts as an integer but without multiplying by 100. For example, to charge ¥500, provide an amount value of 500." SOURCE : https://stripe.com/docs/currencies – ChatGPT Oct 31 '21 at 06:08
  • 4
    @NarutoSempai the link you provided references pricing in invoices; not how Stripe represents currency values in their API. – ChatGPT Oct 31 '21 at 06:10
0

This is not a direct answer, but an example of why float is not the best data type for currency.

Because of the way floating point is represented internally, it is more susceptible to round off errors.

In our own decimal system, you’ll get round off errors whenever you divide by anything other than 2 or 5, which are the factors of 10. In binary, it’s only 2 and not 5, so even “clean” decimals, such as 0.2 (1/5) are at risk.

You can see this if you try the following:

select
    0.1::float + 0.2::float as floats,          --  0.30000000000000004
    0.1::numeric + 0.2::numeric as numerics     --- 0.3
;

That’s the sort of thing that drives auditors round the bend.

Manngo
  • 14,066
  • 10
  • 88
  • 110
0

My personal recommendation is decimal with the precision according to your needs. Decimal with precision = 0 can be the option if you want to store the integer number of currency minor units (e.g. cents) and you have troubles handling decimals in your programming language.

To find out the needed precision you need to consider the following:

  • Types of currencies you support (they can have different number of decimals). Cryptocurrencies have up to 18 decimals (ETH). The number of decimals can change over time due to inflation.
  • Storing prices of small units of goods (probably as a result of conversion from another currency) or having accumulators (accumulate 10% fee from 1 cent transactions until the sum reaches 1 cent) can require using more decimals than are defined for a currency
  • Storing integer number of minimal units can lead to the need of rescaling values in the future if you need to change the precision. If you use decimals, it's much easier.

Note, that you also need to find the corresponding data type in the programming language you use.

More details and caveats in the article.

Cardinal
  • 1,321
  • 1
  • 11
  • 5