Rietta.com Security logo
You are reading The Rietta Blog, a publication about the web since 2005.

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.

1
2
3
4
5
6
7
8
9
10
11
12
[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,

1
2
3
[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 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.

Numeric Types

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

1
2
3
4
5
6
7
8
9
10
11
[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,

1
2
3
4
[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. 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.

See Also

About Christopher Choi

Mr. Choi's role is to both development and plan out projects so tasks can be triaged so work is delivered in a timely manner to clients.

Christopher is a full-time developer on the Rietta team. Being one of the newer members of the team, he has assisted in creating systems for project management and client communications.

His public key is available at chrisgopher on keybase.