The best (and worst) types for storing money in PostgreSQL

Storing Money in a Database is not as easy as you might think. So, in this video, we look at some of the types you can use in PostgreSQL, and perhaps, why you may not want to.

This video was sponsored by Brilliant.

Join this channel to get access to perks:

[kazwalker]

One downside with your currency approach is that is requires a schema migration to add types, which requires engineering involvement to deploy this change. A more adaptable solution is to have a table of currency codes with a pkey of type citext, then use a foreign key constraint. This allows currencies to behave like an enum that can be updated without schema changes, while keeping the currency code local to the row that has price info so you can avoid having a join in the query. The currencies table approach also provides a convenient location to store metadata about the currency, such as it's symbol, format string for localized display, and it's subunit integer representation (such as 100 for the various dollars). You also have a table to reference when you start inevitably needing to save time-based relative value pairs for converting between currencies.

This approach also fits nicely into double entry based systems and can be made to be GAAP (Generally Accepted Accounting Principles) compliant. Lastly, it makes the system easier to reason about for accountants, which is important if your system is to ever handle real currencies.

[ultru]

Wish our CEO could've watched this vid 10 years ago. We use floats for everything in Python, including monetary calculations, and it's a never-ending source of headaches.

[potatomaaan]

Since im not a PHP developer, I've thankfully never had to worry about this sort of stuff before

[medabottpro]

Before seeing Decimal, the way I have been doing this would be taking a BIGINT, and controlling for decimal precision by multiplying the value with 10^n for (n decimal places). So mul 1000 (for 3 decimal places). So this would represent 100.34 as 100340 on DB. Had nice utils to help convert between on and another during read (something like V DIV 1000) and during write with v * 1000. Was great at first, but the overhead required to remember this early one was tricky. Please use NUMERIC, It's not worth the trouble.

[hakuna_matata_hakuna]

do a 10 minute postgres rash course am loving this

[Felinaro]

Dont forget about composite types: that way one will be able to not only store numerical value and currency into the single field, but also define custom functions for processing them, with currency checks, to not allow addition of price values in different currencies.

[mahammadisgandarli]

I remember making these mistakes several years earlier. And made research for every type to check which one will be okay. I came to these conclusions except using decimal/numeric was a news for me in this video. Because I remember when I retrieved decimal value from db php accepted it as float which in turn didn't solve a problem for me.

[wlockuz]

Wouldn't it be better to have a separate table of ISO currency codes to make it easier to add or remove currencies? With the current solution you would have to modify the table definition to add a new currency, where as with a separate table you just insert a new row with the desired currency.

That said, I'd definitely love some more postgres stuff!

[Elesario]

Nice to see someone calling out floats for money value manipulation. I've dealt with a number of devs that need to learn this lesson.
I'd use NUMERIC or it's equivalent in any database that supports it. I'd only make exceptions if there's some very specific controls that make a more limited type optimal, and even then I'd feel guilty if forced to it.

[herozero]

Thank you for such an informative video.
I'm now definitely more informed on the different postgres types.
As an engineer learning to self-host databases I can really use such videos.

[Septumsempra]

As someone who's developed a price comparison app, I ask: why are you only releasing this video now after I've shot my foot repeatedly?
🇿🇼🇿🇼🇿🇦🇿🇦

[jeeukko]

I have a table with all possible int values inserted and just use the row id
/s

[rodjenihm]

I wouldn't be surprised if there is Postgres extension that handles currency as well.

[benwilson]

I didn't even know there was a money type. But your explanation, which only took 2 and 1/2 minutes, proves that it is the best type.
The caveats of 'dont do division' and 'be careful when changing currencies' are perfectly reasonable, and so yeah the money data type is great. Thanks for putting it at the beginning.

[srki]

I use double to represent money but the smallest unit I want to represent is 1. So if I want one cent to be my smallest unit then $3.5 would be stored as 350 as double. Didn't have any problem with it so far. My currency table also has a column that represents the smallest unit. With double I can also represent some really large values.

[CraigLuna]

One crazy aspect I've had to deal with was many currencies (Labor/Material/Equip...) for the unit costs and that varies per item. The exchange rates could be different as contingencies determined potential fluctuation over time and when the purchase or building would occur. Reports and editing would be based on the view or report base of the user ( Show in USD regardless of the mixed currencies). Hundreds of thousands of items per job and real-time editing in an Excel like fashion.


Remember exchange changes can be complex. If quoted at 100GBP, it will stay 100GBP regardless of any exchange rate changes even though the USD view shows a cost change.

[zaafhachemrachid]

Thx you soooo mush,
i get my first real world project yesterday and I was lost what type i should use for coruncy, thx you are the best

[OldKing]

This is one thing that I do like about SQLite as well is that it does have a NUMERIC "Affinity" type. Which means that it does something spooky in the background and is probably inefficient since it doesn't take/listen to precision and scale.

[doxologist]

Fantastic vid. This is a commonly googled issue that insites much discussion. Ive ran into this often and even after making a decision, kept double guessing myself. I like how you break down all the problems of the types here :)

[docopoper]

One other difficulty I see in using numerics over integers is that programming language support for these types of values isn't quite as universally good as you'd hope.