`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