Two important points about
- 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
DECIMALare close but not identical according to the SQL Standard. In SQL:92, your specified precision for
NUMERICis respected, whereas for
DECIMALthe database server is allowed to add additional precision beyond what you specified. Here again Postgres strays a bit from the standard, with both
DECIMALdocumented as equivalent.
- 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:
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.
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.
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
NUMERIC in SQL and
BigDecimal in Java.
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.
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
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.
BigDecimal is definitely slower than using Java’s floating-point types,
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