Best IT Recipes

PostgreSQL: What data type should be used for currency?

The Money type seems to be discouraged, as described here

My application should store the currency, what type of data should I use? Numeric, money or FLOAT?

+100
sql database postgresql database-design
Mar 31 '13 at 5:06
source share
5 answers

Numeric with a forced accuracy of 2 units. Never use float or float as a data type to represent a currency, because if you do this, people will be unhappy if the financial report figure is incorrect or + a few dollars.

The type of money just left for historical reasons, as far as I can tell.

+75
Mar 31 '13 at 11:03
source share

Your source is in no way official. This applies to 2011, and I don’t even recognize the authors. If the type of money were “discouraged,” PostgreSQL would say so in the manual, but it is not .

For a more official source, read this thread in pgsql-general (this week only!) With statements from major developers, including D'Arcy JM Cain (the original author of type money) and Tom Lane:

Mostly money has its (limited) use. The advantage over numeric is performance.

decimal just an alias for numeric in Postgres.

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

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

Personally, I like to store the currency as an integer representing cents. This is more effective than any of the other options mentioned.

+92
Mar 31 '13 at 12:56
source share

Your options:

  • integer : save the amount in cents. This is what EFTPOS transactions use.
  • decimal(12,2) : keep the sum accurate to two decimal places. This is what most basic books use.
  • float : a terrible idea - inadequate accuracy. This is what naive developers use.

Option 2 is the most common and easiest to use. Make the precision (12 in my example, which means 12 digits) as large or small as best suits you.

Please note: if you are aggregating several transactions that were the result of a calculation (for example, using the exchange rate) into one value having a business value, the accuracy should be higher to ensure the exact value of the macro; consider using something like decimal(18, 8) so that the sum is accurate and individual values ​​can be rounded to the precision for display.

+55
Mar 31 '13 at 11:18
source share

I save all my money fields as:

numeric(15,6)

It seems excessive to have so many decimal places, but if there is even the slightest chance that you will have to deal with several currencies, you will need such accuracy to convert. No matter what I present to the user, I always keep the dollar. Thus, I can easily convert to any other currency, taking into account the daily conversion rate.

If you never do anything but one currency, the worst thing here is that you spent a little space on storing zeros.

+18
Oct. 16 '15 at 18:52
source share

Use a 64 bit integer stored as bigint

I recommend using micro-dollars (or a similar base currency). Micro means 1 million, so 1 microdollar = $ 0.000001.

  • Sufficient accuracy to handle a cent fraction.
  • Works well at a very low cost per unit (for example, ad impressions or API fees).
  • Easy to use and compatible with any language.
  • Smaller data to store than strings or numbers.
  • It is easy to maintain accuracy with calculations and apply rounding at the final output.
+8
Jul 09 '18 at 5:34
source share


More articles:


All Articles