Storing and querying monetary values in Postgres and Hasura
Data types you should and shouldn‘t use for financial information in PostgreSQL, and risks specific to JavaScript and Hasura.
Whatever it is you’re building, there is a good chance you’ll want to monetize the project in the future.
And with monetization, come the many challenges of storing and manipulating financial data.
Below, I will look at the data types you should and should not use for monetary information in Postgres. I will then discuss additional considerations when using these types with JavaScript and Hasura.
Monetary types in PostgreSQL
⚠ Real/Double Precision (Float)
Used to store variable-precision, approximate values.
The type many beginner developers unknowingly choose to store financial transactions is floating point.
After all, Float is the default type for numbers in common languages including JavaScript, and typing const price = 4.99 for a $4.99 item just makes intuitive sense.
The problem is, floating point arithmetic is not 100% accurate.
Just try running the JavaScript code below, as an example:
Or a similar script in Postgres:
Such imprecision can lead to substantial financial losses at scale. Therefore, monetary data should never be stored as Float, and the type should generally be avoided.
Numeric/Decimal
Used to store exact values, with practically unlimited, user-specific precision (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point).
To work around the Float precision problem, Postgres provides the Numeric type, and its alias, Decimal. These types can store numbers with a very large number of digits, and yet perform calculations exactly.
Let’s rerun our SQL loop from earlier, with type Numeric instead of Float:
If you are only dealing with USD and sums smaller than a billion dollars, Numeric(12,2) should give you just enough precision.
If you are converting between multiple currencies, or deal with commodities priced in fractional cents (such as gas), use the larger Numeric(18, 8) instead.
Money
Used to store a currency amount with a fixed fractional precision.
Unlike MySQL, Postgres also offers a dedicated Money type which stores currency amounts with a fixed fractional precision.
The input can take many forms including integers, floats, and even strings such as ‘$20.00’. The output is also automatically formatted as a currency value:
Both fractional precision and the currency symbol are determined by the database or environment LC_MONETARY setting.
This means you cannot simultaneously operate with $ and ¥, and the Money type is thus discouraged if you deal with more than a single currency.
Note that division of a Money value by an Integer is performed with truncation of the fractional part towards zero.
To get a rounded result, cast the Money value to Numeric before dividing, then back to Money.
If high performance is important for your use case, operations on Money type values are slower than for Numeric or Integers.
Int/Integer
Used to store values without a decimal, between -2147483648 and +2147483647.
To avoid Float precision problems, a common way to deal with monetary values, particularly in JavaScript, is to use positive integer values representing how much to charge in the smallest currency unit.
The division of integers and multiplication by decimals may still result in inexact values, but comparisons, and basic integer addition, subtraction, and multiplication will be accurate.
If you’ve ever worked with Stripe, you’re already familiar with this pattern. Stripe API returns $1.00 as 100 (cents), and ¥100 as 100 (as yen is a zero-decimal currency).
Note that the max safe Integer in Postgres is 2,147,483,647.
This means you can only use Integers to store approximately 2 billion cents, or 20 million dollars.
If you need to handled fractions of a cent, such as for currency conversion, gas prices, ad impressions or API charges, you’ll need to use integer micro-dollars ($0.000001) instead.
This leaves us with a maximum stored value of just $2,147.
If high performance is important for your use case, operations on Integer are faster than on Money, Numeric and Strings.
BigInt
Used to store values without a decimal, between -9223372036854775808 and +9223372036854775807
If the maximum value of an integer is just over 20 million dollars, how can Stripe process billions every day?
Enter BigInt, a type that can store values up to quintillion cents, or quadrillion dollars!
Even if you decide to store values as micro-dollars, BigInt gives you over a trillion to work with.
Note that until recently, JavaScript had no support for BigInt, and the new type introduced in ECMA-262 still requires a polyfill to work in the Safari browser (including iOS).
Note also that in a BigInt division, fractional results are truncated, not rounded:
Finally, if you only exceed maximum Integer values in grouped data, remember that you can always cast the individual values to BigInt before calling your aggregation function:
If high performance is important for your use case, note that operations on BigInt are slower than on Integer, but faster than on Money and Numeric.
Dealing with money in Hasura
After reading through the above, or the countless arguments on StackOverflow and elsewhere, you may have already decided on one type or another to store your financial data.
Perhaps it’s Money for formatting and ease of use, or Numeric for intuitive precision.
But wait, if you are going to use the data with Hasura and JavaScript in the end, there are several more issues to consider!
Risks of using Numeric type with JS
Numeric is perhaps the most popular type for currency values in the Postgres community.
It’s intuitive enough, efficient enough, and has no problems with rounding.
However, this means you’ll effectively get a float in JavaScript once you query the data. You have to remember to multiply the value by 100 (or more if dealing with fractional cents) before any operations, then divide by 100 before storing the end result.
The same concern exists for the Money type.
Risks of using Integer type with JS
Integers, the most popular option for storing currency values in the JavaScript community, are fairly straightforward to work with, but there is one major concern to keep in mind.
Whereas the MAX_SAFE_INTEGER in JavaScript is 9,007,199,254,740,991, the maximum Integer value in Postgres is just 2,147,483,647 (same as the maximum safe integer size for bitwise operations in JS).
If you are dealing with very large, or very small currency amounts, you therefore face a risk of exceeding the maximum value in Postgres, without running into any errors in your JS code.
To avoid this issue, consider using your own integer limit constant, instead of relying on Number.MAX_SAFE_INTEGER.
Mutating and querying BigInt values with Hasura
BigInt is perhaps the most robust and performant solution if you need to deal with very large (or very small) values in your system.
It is important to remember, however, that JSON does not support this type, and large integers will be capped to MAX_SAFE_INTEGER when returned in a query!
To overcome this problem, enable stringification of Postgres numeric types with the --stringify-numeric-types flag, or a HASURA_GRAPHQL_STRINGIFY_NUMERIC_TYPES=true environment variable.
With this option enabled, Hasura will represent BigInt (and Numeric, Decimal, etc.) as Strings instead, and accept the same format in mutations:
In Node.js, you then have to remember to convert the String into a safe value with the ES6 BigInt() function, and if you need to do operations on a Safari client, use a BigInteger polyfill.
And last but not least, you should avoid using the Hasura console UI to insert or update any such large values, as those too will be capped at JavaScript’s MAX_SAFE_INTEGER in the end.
