1

I have this fintech application (written in Ruby with some JS micro-services) which stores transactions in a database table, amounts are stored as integers in cents, eg $123.45 is stored as 12345, -Є56.78 is stored as -5678. The transactions table currently has about 400 million entries across all partitions. Amounts are typically under 4 digits with under 1% being over 4 digits and under 0.1% over 6 digits.

This has worked pretty well for me for a number of years but now I am facing two issues:

  1. some amounts in a few specific currencies can't be stored eg Rials since $1 would be about 45000 Rials, so far I just didn't offer support for these currencies and it caused some loss of business.
  2. for the same reason can't store amounts in a large number of cryptocurrencies/tokens including Bitcoins

The time has come to sort this out...hopefully without breaking any of the Ruby or JS applications as I know both languages and also the database will have some issues with floating point numbers.

What data type should I use to store these amounts?

Improve this question
6
  • 4
    You need an exact data type (not float) to store currency amounts. This comment applies to both your app and underlying Postgres. Commented Aug 17 at 12:54
  • You can use t.decimal for RoR but ruby reads it as a string. You will have to handle that in your code. Commented Aug 17 at 13:12
  • As it stands, the question only mentions the presence of Ruby and JavaScript, which I don't think is enough to justify their tags. As long as it remains limited to the db-side data type for currency, it's a duplicate but I'm happy to vote reopen if you could provide more context on your db-to-Ruby/JS mapping or some data characteristics (high throughput of transactions, sub-cent granularity), DDL, examples, making the question more specific. Commented Aug 17 at 17:28
  • This isn't just a database-related issue, different languages have different issues with large numbers and decimals. Certain issues that might be solved on the database side by using big/long integers might generate other issues in JS and even more different issues in Ruby. I was hoping to hear from people who operate similar stacks (polyglots) and have dealt with this previously but I guess it doesn't matter now that the question is closed... Commented Aug 17 at 20:11
  • 1
    Please edit the question to make it non-duplicate, clear&detailed, focused and non-opinion-based (I'm just listing the first page of "vote close" slots). I just reversed my close vote, which also somehow reversed someone else's of different category, and I'm leaving a +1 as encouragement, but none of that shields this thread in any way from any of the close/flag types that are currently applicable. I genuinely think if you add just a few more details of your specific case, you're good. Please do also take a look at the other 2 sites to broaden your research. Commented Aug 17 at 20:53

2 Answers 2

Reset to default
5

Use numeric/decimal.
Do not use the built-in type money.
Both int ant bigint are too small to deal with amounts that are pretty usual in crypto traffic, plain int is too short to handle heavily inflated fiat like LBP, LAK, UZS or the IRR you mentioned.

Name Storage Size Description Range
numeric variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

If you're also looking to guard the validity at db level, you might be interested in a custom constraint trigger. Crypto can be defined pretty arbitrarily and there are even official, real non-decimal 1/5 currencies still in circulation, 1/10, 1/1000 as well as ones that are indivisible (no cents). That suggests often recommended (precision,scale) typemod might not be enough.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I'll just mention the -1 on the other answer isn't mine and that I'm happy to correct this, if you comment what's incorrect or simply go ahead and edit it in. My plan is to eventually move this to the thread I linked this one to, but only after I make sure the crypto/inflation part is not already covered over there - share your doubts now, I'll appreciate you saving everyone's time. Otherwise be sure to follow the other thread and chase me there, that's two downvotes for the price of one mistake.
In addition (from the docs, emphasis mine): "The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required."
1

Keep using integers, but stop assuming one global “cents.” Store amounts as a signed BIGINT in the currency’s smallest unit (atomic unit) and add a currencies table that records decimals for each currency/token (USD=2, BTC=8, many tokens=6–18). This stays exact, compact, and fast (no floats) and handles rials/crypto.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.