Storing currency in PostgreSQL

There are different ways we can store currency in PostgreSQL, this blog post will cover the money and numeric types.

Money 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, '$20.00'.

So for example, we can add different inputs into the money type which will store the currency amount with the mentioned fixed fractional precision.

[local] =# CREATE TABLE money_example (cash money);
[local] =# INSERT INTO money_example VALUES ('$99.99');
[local] =# INSERT INTO money_example VALUES (99.99);
[local] =# INSERT INTO money_example VALUES (99.98996998);

[local] =# SELECT * FROM money_example;
  cash
------------
  $99.99
  $99.99
  $99.99
(3 row)

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,

[local] =# INSERT INTO money_example VALUES ('¥99.99');
ERROR:  22P02: invalid input syntax for type money: "¥99.99"
LINE 1: INSERT INTO money_example VALUES ('¥99.99');

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(unless configured via lc_monetary settings) can cause calculation errors. As the money type is also a numeric type, we could cast our money column as numerics with our preferred precision.

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.

Numeric Types

Another known approach is to use the Numeric Type which is an alias to Decimal. As the Float type can also handle money, the official psql docs 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 and scale.

• 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.

[local] =# CREATE TABLE numeric_example ( numeric_cash NUMERIC(6, 4) );
[local] =# INSERT INTO numeric_example VALUES('99.9999');
[local] =# INSERT INTO numeric_example VALUES('99.998550');
[local] =# INSERT INTO numeric_example VALUES('99.998549');
[local] =# SELECT * FROM numeric_example;
  numeric_cash
---------------
  99.9999
  99.9986
  99.9985
(3 row)

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 999.998550,

[local] =# INSERT INTO numeric_example VALUES('999.99850');
ERROR:  22003: numeric field overflow
DETAIL:  A field with precision 6, scale 4 must round to an absolute value less than 10^2.
LOCATION:  apply_typmod, numeric.c:5998

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

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.

In Conclusion

With numerics, we can be specific with total digits and to what degree the fractional decimal point will round. When handling currencies with a database, 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.