Which datatype should be used for currency?

Question

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?

Nisse

If you have read the whole thread, Numeric is the way to go. – Magnificent

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 (SO) and ISO 4217 (Wikipedia). The short answer is that you'll need two columns. – Undertrick

Don't use money – Dann

Answer 1

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.

Guthrey

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.) – Claussen

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. – Juliettejulina

@NikolayArhipov good point, so the max is actually scale - precision – Tad

numeric(3,2) will be able to store max 9.99 3-2 = 1 – Tad

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. – Overijssel

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. – Overstretch

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. – Elvieelvin

Answer 2

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:

• Jasper Report: unable to get value for field 'x' of class 'org.postgresql.util.PGmoney'

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.

Vociferate

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 – Dann

@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. – Vociferate

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!!! – Agama

@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. – Vociferate

@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. – Agama

If you store as an integral number of cents, what do you do about prices with fractional cents, like gas prices? – Phrygian

@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. – Vociferate

what about a scaled integer? 1000.45 numeric(6,2) (6 bytes) stored as 100045 (4 byte) – Roxi

@PirateApp: Yes, my personal favorite. You may have missed the last sentence of my answer, saying just that. – Vociferate

Note that the Postgres wiki discourages the use of money: wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money – Dann

@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. – Vociferate

Answer 3

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.

Fleisher

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. – Sublieutenant

There is a 4th option - that is to use a String and use an equivalent non-lossy decimal type in the host language. – Chipmunk

what about a scaled integer, surely storing 10000.045 wouldnt hurt if it was stored as 10000045 with a 1000x scaling factor? – Roxi

Answer 4

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.

Obcordate

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. – Overijssel

Why this over numeric(15,6) suggested in another answer? – Elvieelvin

@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. – Obcordate

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 developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… 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. – Elvieelvin

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 which would be 4 bigints (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 :) – Elvieelvin

@JuliuszGonera there are JS libraries for large numbers. – Titanesque

Answer 5

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.

Arms

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. – Standoff

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. – Arms

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. – Standoff

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. – Arms

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. – Hughett

Answer 6

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...

Wattenberg

This is what Stripe does Do you have a source for this statement? – Drug

@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 : stripe.com/docs/currencies – Wattenberg

@NarutoSempai the link you provided references pricing in invoices; not how Stripe represents currency values in their API. – Wattenberg

Current text from the link: "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 (that is, 1000 cents)." – Protuberancy

if people still used SO, this would be the top-voted answer ... – Wattenberg

Answer 7

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.

Beneath

Answer 8

An updated thought to storing currency, here's the logic on why I store numeric(33,18):

Firstly, I store USD.CENTS, GBP.PENCE, EUR.CENTS, ETH.WEI, with major unit on the left of the dot and minor unit on the right of the dot. To visualise:

100,000,000,000,000.000000000000000000

For the right side of the dot:

Fuel prices can be fraction of cents/pence/etc such as 139.73p, therefore you can't simply store to 2 decimal places or you have to make a decision on rounding when saving and if you are a fleet company who rounds 100's of trucks daily, your final figures will eventually be out - although ultimately, the supplier will do their own rounding and give you the final price so you are in their mercy of how they round.

Exchange rates are usually quoted to 6 decimal places, such as 1 GBP = 53,398.617297 IRR but again, you are at the mercy of the seller as to how they round.

The real reason I use 18 decimal places: cryptocurrency.

To support todays evolving world, "currency" includes cryptocurrency and so far the ERC20 token, such as Ethereum (ETH), has the most with a fraction of 18 places, however 1 wei (minor unit of ETH) is worth 0.00001971 USD but what if someone trades 1000 to make it to 1 cent and wants to record all those transactions.

As for the left side, why 33:

First, it's not 33 on the left. The left number is the total of numbers stored on both sides of the dot, so on the left of the dot is actually only 33-18= 15 numbers .

15 numbers equates to 100,000,000,000,000, or in words, 1 hundred trillion. Any single transaction worth that, will probably not be entered in to the system I am building.

The largest crypto currency value today is BTC with a value of 41,385 USD which is a long way off 1 hundred trillion.

Why so high you ask? Optimism, edge case, program and forget... whilst there does exist trillion USD companies, I doubt they will use my system but I will email them and ask :)

Also, 1 USD = 148.02 JPY so 1T USD equates to 148,018,001,949,397.06 JPY so to ensure both sides of the transaction can record an entry, I use 100 trillion.

Hence the reason for storing numeric(33,18).

As a bonus, the storage space on disk in postgres is two bytes for each group of four decimal digits, plus three to eight bytes overhead. Which equates to a maximum of 2*(33/4)+8 = 25 bytes for the biggest number.

On closing, if youre using Postgres, then just setting the column type to Numeric will allow precision and scaling to the maximum available (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and future proof your database, although does not ensure portability. You just need to ensure that you are rounding to the relevant decimal when you insert/update the database. https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

Acoustic

Answer 9

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.

Trapper