The best type for storing money in our posgresql db

As a software developers we often deal with money, not that money you get from being a "php" developer :), definitely not, i mean storing money data in a Posgresql db.

To clear this out imagine you are running a small startup which is a courses website, you wanna store the payments data containing course name and price with the currency,
Well it's gonna be like this

Now which type should we use in this price column?!

1- MONEY type

This is the first type that may come to your mind you know it makes sense right ?! It's a type money for Money for sure,
But it's not good as you think, it holds limitations

• it can't handle fractions of a smallest currency unit like 0.01$ for example.
• the money is formatted to USA dollar by default, you might ask is that a problem?! I can change it from by changing the "lc_monetary" value, well you are right but that continent for local apps not international app like courses app for example because it can't hold multiple currencies at the same time and it might me a source of bugs for large large app.

2- FLOAT type

Which is represented by "REAL" and "DOUBLE PRECIOUS",

You should never ever think about this type at all, why ?! It is just the way floats are handled by posgresql and programming languages in general, 0.1 in math is not 0.1 in binary, some floats can't be accurately represented like the 0.1 number.

3- INTEGER type and BIGINT type

They can't store decimals which is not good for some apps that needs decimal points.

4- VARCHAR type

This type is just as bad as using floats, and it doesn't support arithmetic operations, so you need to convert it to another type and use it in mathematics for example.

5- NUMERIC type

It has alot of pros:

1. Supports decimal points.
2. Supports large numbers of decimal places up to 16k in total.
3. It's able to store large numbers up to 1e131071 number before the decimal point.

• you can use it like this NUMERIC(A, B), where A is the precision, which defines the total number of degits that numeric can contain, And B is the scale which is the amount of numbers after the decimal place.

And it come also with some hidden cost "variable memory usage" hence it requires at least 8 bytes of data with size increasing depending on the value.

Comparing this to INTEGER -> 4 bytes,and BIGINT -> 8 bytes respectively, you can handle this by controlling the scale and precision vlaues.

But it lacks the associated currency so there's a solution for This is creating another column that expects currency type only like this table down,

In conclusion you have to be aware of using each of these types each one of them serve some apps but it's worse in others, i hope you liked this article.

#wild_duck