How to store amounts with currencies?

Question

PostgreSQL

Actually, once again I decided to reconsider the approach to storing amounts and standardize it for myself forever and ever. An ideal solution that never causes pain has not yet been met / developed.

The approach to the numbers themselves is simple: decimal (
19,4 ) code, according to the judge, both can act as id and the use of char (3) is a little clearer if you climb your hands into the database. Here in thought. But the amounts can be stored in different ways: - As three fields: price, currency_id, date (well, either take the date of the document / current date, depending on the type of record)


- As a composite type: create type money as (value decimal(19,4), currency_id int, date date default null)
- 9 years ago there was such a thing https://github.com/samv/pg-currency but no development received, and very sorry.

Obviously, storing in a complex type simplifies insertion/output but complicates joins/conversions.

Storage in different currencies completely eliminates any aggregation by means of a DBMS (this is exactly the problem that pg-currency was designed to solve), or it seriously complicates it, reduction to one carries a loss of accuracy. In theory, you can write conversion functions for a complex type, but I'm not sure how fully implemented this is.

However, from the point of view of the exchange protocol with the frontend, it is still better to represent any amount as an object.

Share your recent experience?

Answer 1

Store in integer units, for example, if it is USD, then in cents, rubles in kopecks, etc.

Answer 2

in data with money, the main aggregation
is debit - credit
and start from this, but stores it for output to the front - yes, at least in JSONb