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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.