Money data on PostgreSQL using Java

Question

I'm writing a Java program that mines currency exchange data. The data can have multiple digits in the decimal such as "0.973047". After some research I was able to find out BigDecimal is the right data type for Java, but which data type should I be using for PostgreSQL?

Answer 1

NUMERIC/DECIMAL

As Joachim Isaksson said, you want to use NUMERIC/DECIMAL type, as an arbitrary precision type.

Two important points about NUMERIC/DECIMAL:

• Read the doc carefully to learn that you should specify the scale to avoid the default scale of 0, meaning integer values where the decimal fraction gets lopped off. While this is one of the places where Postgres strays from standard SQL (giving you any scale up to the implementation limit). So failing to specify the scale is a poor choice.
• The SQL types NUMERIC & DECIMAL are close but not identical according to the SQL Standard. In SQL:92, your specified precision for NUMERIC is respected, whereas for DECIMAL the database server is allowed to add additional precision beyond what you specified. Here again Postgres strays a bit from the standard, with both NUMERIC & DECIMAL documented as equivalent.

Terms:

• Precision is total number of digits in a number.
• Scale is the number of digits to the right of the decimal point (the decimal fraction).
• ( Precision - Scale ) = Number of digits to the left of decimal point (integer portion).

Be clear on your project's specs for precision and scale:

• Big
  The precision must be big enough to handle larger numbers that might be needed in the future. Meaning… Perhaps your app today works in amounts of thousands of USD but in the future must perform roll-up reports that end up in the millions.
• Small
  For some accounting purposes, you may need to store a fraction of a the smallest currency amount. Meaning… More than 3 or 4 decimal places rather than the 2 needed for a penny in USD.

Avoid MONEY type

Postgres offers a MONEY type as well. That may sound right, but probably not best for most purposes. One downside is that with MONEY the scale is set by a database-wide configuration setting based on locale. So that setting can vary dangerously easily when you switch servers or make other changes. Furthermore, you cannot control that setting for specific columns, while you can set the scale on each column of NUMERIC type. Lastly, MONEY is not standard SQL as shown in this list of standard SQL data types. Postgres includes MONEY for the convenience of folks porting data from other database systems.

Move the Decimal Point

Another alternative employed by some is moving the decimal point, and just store in large integer data type.

For example, If storing USD dollars to the penny, multiple any given fractional number by 100, cast to an integer type, and proceed. For example, $123.45 becomes the integer 12,345.

The benefit to this approach is faster execution times. Operations such as sum are very fast when performed on integers. Another benefit to integers is less memory usage.

I find this approach annoying, confusing, and risky. Annoying because computers should be working for us, not against us. Risky because some programmer or user may neglect to multiply/divide to convert back to fractional number, giving incorrect results. If working in a system without good support for accurate fractional numbers, this approach might be an acceptable workaround.

I don't see any advantage to moving the decimal point when we have DECIMAL/NUMERIC in SQL and BigDecimal in Java.

Rounding & NaN

In your app’s programming, as well as any calculations made on the Postgres server-side, be very careful and aware of rounding and truncation in the decimal fraction. And test for inadvertent NaNs popping up.

In both sides, app and Postgres, always avoid floating point data types for money work. Floating point is designed for performance speed, but at the cost of accuracy. Calculations may result in seemingly crazy extra digits in the decimal fraction. Not good for financial/money or other purposes where accuracy matters.

BigDecimal

Yes, in Java, you want BigDecimal as your arbitrary precision type. BigDecimal is slower and uses more memory, but will accurately store your money amounts. SQL NUMERIC/DECIMAL should map to BigDecimal as discussed here and on StackOverflow.

BigDecimal is one of the best things about Java. I don’t know of any other platform with a similar class, especially one so well-implemented and well-honed with major enhancements and fixes made over the years.

Using BigDecimal is definitely slower than using Java’s floating-point types, float & double. But in real-world apps I doubt your money calculations are going to be any bottleneck. And besides, which do you or your customers want: the fastest money calculations, or accurate money calculations? 😉

I have always thought of BigDecimal as the biggest sleeper feature in Java, the most important advantage to using the Java platform over so many other platforms lacking such sophisticated support for fractional numbers.

Similar question: Best Data Type For Currency

Answer 2

To get as good (and exact) precision as possible, you can use NUMERIC (or its alias DECIMAL), which has high precision and allows you to decide the precision you require;

NUMERIC

User-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Answer 3

Generally, money shouldn't be stored as floating-point. The best approach is usually to store the amount of money as an integer of the smallest allowable size (for example, one U.S. cent) and format it for input and display. This is essentially what a fixed-precision DECIMAL column does in SQL, but if you transfer it back into Java, you still run the risk of losing precision (e.g., what happens if you split exactly half of the last allowable digit)?