There are different ways we can store currency in PostgreSQL, this blog post will cover the money and numeric types.
Unlike MySQL, PostgreSQL supports Money Type which stores currency amounts with a fixed fractional precision. That fractional precision is based on what you may set on the
ic_monetary locales when formatting monetary values in your database.
How you can configure that can be found on the official PostgreSQL documentation. When working with monetary types, the input can take many forms like integers, floats, and more commonly,
So for example, we can add different inputs into the money type which will store the currency amount with the mentioned fixed fractional precision.
1 2 3 4 5 6 7 8 9 10 11 12
If you plan on supporting multicurrency support like €(euro) or ¥(yuan), the currency symbol can throw errors if $(dollar) is set in the locale.
If we try to add ¥99.99 into our table,
1 2 3
The only way to support Yuan would be to change our locale from $ to ¥ in order to insert and view values with said currency symbol.
If we were doing more sensitive calculations for accounting or finance, using the money type with its fixed precision can cause calculation errors. One could configure locales to better support the system being designed, but it may be more beneficial to configure the numeric type to better serve those calculations.
The more preferred approach is to use the Numeric Type which is an alias to Decimal. Contrary to what the official psql docs have for the money type, they recommend using numeric type when handling monetary types:
The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required.
We can set a column
NUMERIC and optionally configure both
precision– are the total number of digits.
scale– are the number of fractional decimal points(decimal digits).
An example can be
NUMERIC(8, 4) which can store a number like
5234.5678. In order to be compliant with Generally Accepted Accounting Principles(GAAP) as mentioned in our other article covering Best Data Types for Currency/Money in MySQL?
, compliancy requires at least four decimal places to reduce rounding errors.
For example, we can create a table to take 6(
precision) total digits with 4(
scale) decimal digits.
1 2 3 4 5 6 7 8 9 10 11
Since we had set the scale as
4, our table will be rounded to our specified fractional decimal points(
scale). We can exceed the number of total digits specified by
precision as long as it is a decimal number. So while
99.998550 rounds to
99.9986, if we insert
1 2 3 4
this would throw an error as the additional digits are to the left of the decimal point and exceeds 2 digits(precision – scale).
Float types are another option but should be used with great caution and are not recommended for storing currencies. Float types by nature are “inexact”, which means that the values are often stored as an approximation. The approximations when interacted with regularly will subtly build up and create rounding errors when exactness is under great scrutiny.
As recommended by the PostgreSQL maintainers,
If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.
With numerics, we can be specific with total digits and to what degree the fractional decimal point will round. Although money type may be more performant, there are a lot variables to be mindful of when doing calculations dependent on the exactness of stored numbers. There is also no reliance on currency type being set in the locale, so multi-currency support has no constraints.
- Best Data Types for Currency/Money in MySQL? (rietta.com)