The best (and worst) types for storing money in PostgreSQL
ฝัง
- เผยแพร่เมื่อ 29 เม.ย. 2024
- To try everything Brilliant has to offer-free-for a full 30 days, visit brilliant.org/DreamsofCode . You’ll also get 20% off an annual premium subscription.
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 inspired by a discussion in the Dreams of Code Discord Server: / discord
This video was sponsored by Brilliant.
Become a better developer in 4 minutes: bit.ly/45C7a29 👈
Join this channel to get access to perks:
/ @dreamsofcode
Join Twitter: / dreamsofcode_io - วิทยาศาสตร์และเทคโนโลยี
![เกิดใหม่ทั้งทีก็เป็นสไลม์ไปซะแล้ว ซีซั่น 3 - ตอนที่ 57 [ซับไทย]](http://i.ytimg.com/vi/pNhft3YZFRE/mqdefault.jpg)
To try everything Brilliant has to offer-free-for a full 30 days, visit brilliant.org/DreamsofCode . You’ll also get 20% off an annual premium subscription.
I use emojis to represent currency
I love this idea so much.
While I wish my account was 🤑 It's more like 😭
I demand emojis for all ISO 4217 currency codes. Not just💵, 💶, 💷 and 💴
🍎= 1000
Can we get a video on the implementation of emoji currency please?
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.
Send it to him
Using floats (with decimals) for money is never a good idea.
Really?? We lost a project for this. It was a project on the company not my team project but they had problems with some rules like that the sum should match the expected value, they were using float on the java side and Float on the database mapped directly by hibernate. I was a Junior dev but I already worked on a project with money and I said to them use BigDecimal in java and Number (12,2) on the database, they said the change is to big it will take too much time, so they decided to use double precision float, after 1 month they couldn't fix all the rounding errors. An the client canceled the project. 🤣🤣
Use bool instead
@@__Brandon__
How much money do we have?!
Yes.
Since im not a PHP developer, I've thankfully never had to worry about this sort of stuff before
One of the perks of not having millions of dollars.
You'll never get that Lambo then, sonny 😂
Storing money like values in DB (PostgreSQL) has nothing in common with the programming language of your choice.
You can use Python, Ruby, Java, C# - but still make mistakes while choosing a money type.
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.
This is actually great advice. One of our consultants worked on a Salesforce org that had to keep track of currencies and their conversion rates over time. This sort of table was the only way to do it.
So good post , thanks, though I'd appreciate it if you could explain this even simpler since a lot of stuff went through my head , thanks in advance
it is very sophisticated
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.
I think that BIGINT or double is better but you should have precision defined in the currency enum because you don't want every currency to have the same numeric format.
@@srki22 the issue I had with BIGINT, was that it required remembering to convert to a regular number on read (like DIV 1000, when showing user balance). Now imagine having to do that DIV operation for different currencies. Like say for USD, DIV 100; for TZS, DIV 1000; NAIRA, div 10000.
That’s a tough battle, and the overhead needed… I’m not that strong.
Personally, just having the currency to control for that externally is more than enough, then again maybe I’m missing something
@@medabottpro9124 I hear you, but there is an elegant solution. Actually several elegant solutions. 1. even if you use numeric, in your backend or frontend you don't have numeric so in order not to have to show 0.30000008 to a user, you will need to use bigint in front end, or a string. If you use string you can just use your db to format the code. 1. In the table currency you can have the number of decimals used so in your stored procedure or a view you would join with that table and use it for formating the output. The code is the same for all the currency because you are using data from the currency table to know what the smallest unit represents.
You can even use triggers to convert input back to bigint, or do that through a stored procedure. If you have to worry about the same thing in the front-end (or Java/C# backend) there is no point of using numeric. But since you use database for calculations (that is the reason for numeric instead of varchar), you can also use database for formating the output and converting the input so your front-end code will not be complicated.
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.
I have a table with all possible int values inserted and just use the row id
/s
This is the best form of data normalization.
do a 10 minute postgres rash course am loving this
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?
🇿🇼🇿🇼🇿🇦🇿🇦
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.
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!
Having a separate table is also a really good choice!
Yes
@@dreamsofcode You mention in the video that Enums are a bad idea in SQL and hence you use a CHECK. A video of the pitfalls of SQL Enum (I've no idea what these are!) would be interesting... Cheers!
Your videos are just great. Thanks!
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 :)
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.
Very well explained. Thanks for sharing!
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.
I wouldn't be surprised if there is Postgres extension that handles currency as well.
Loved your video and ohh the animation just lovely ❤️❤️
Thank you!
Just now I subscribed to your channel and got notification.
Your video on nvim helped me a lot. ❤️
Very useful and informative video. Thanks bro 😊
Awesome video as usual!
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.
great video! Really enjoyed it ❤
Great video, thank you 🙏
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.
Awesome material!
This is exactly the kind of video that I like
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
Question, if you opt to use the numeric type, do you have to do all arithmetic operations in the database?
I can imagine that converting between the database types and your programming language types might be inconsistent.
Thanks for teaching this!!!
I thought this was obvious but the evidence shows that is not.
Thank youuuu❤
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.
This guy is better than fireship
No (╯°□°）╯︵ ┻━┻
just what I needed
What's the advantage of using a CHECK statement instead of an ENUM?
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.
SQL tips. Love it.
Brilliant video! I can see the benefit with using Integer to represent money, but I was thinking about the rounding errors. In software development, wouldn't it make more sense to do the money-related calculations outside the database, and then just update the value to the database? If you use some very specific money calculation framework outside the database, that way there is no way PostgreSQL would make any mistakes with rounding for example? Yes, it would be very costly to select thousands of rows of data just to do simple sum, but with divisions and such, would this be viable solution?
For a project of mine, I used integers, but had an associated table which stored information on the currency, like decimal places, mostly because I didn’t know Numerical existed, and wanted to avoid and and all decimals.
i store the value on both integer and decimal/numeric columns. Example, on integer the value is 2908, and on decimal the value is 29.08.
Never had to deal with this, but this seems like a good idea. I already thought the numeric would be the winner, but I hadn't heared about the money type, or setting the precision of a numeric yet.
The only thing missing in the discussion is what to set the precision to for your numeric. The currencies shown in the video (USD, EUR, GDP) only use 2 decimal places, but would it be wise storing like 4 digits after the comma, to maybe handle something costing like 1.4532$, maybe as in a price per liter?
what a wonderful video
Wow, that was a great explanation! I liked how you presented it and your pacing. Perhaps you could create a course, like a Postgres tutorial from beginner to advanced levels? I will buy it or become member
Thank you! That's a great idea.
I'm currently working on a Go course, but I'll probably do a PostgreSQL one after!
Is there anything specific you'd like to see on it?
It's also possible to CREATE TYPE and roll your own "money" type that uses numeric under the hood and stores the currency in iso format. The iso currency could also be managed by using a CREATE DOMAIN.
I'm not sure how good that plays along with database drivers/application code.
I personally think that integer/bigint would be a better approach considering the programming language too. Which in many cases does not have a good float number representation and calculations that maybe are needed will have flaws, just as mentioned in the video, and doing that with integers is much easier and precise.
If numerical values take up variables amounts of memory, would that have an effect on query performance (sequential scans perhaps)? I imagine the variable memory means the db can no longer jump by an offset number of records, it now has to account for the size of each record. Im sure there are various factors that would determine this, but what are your thoughts?
That's a great question. I don't know whether variable sized fields affect sequential scan performance or not. If they do however then I assume a TEXT or unsized VARCHAR would also affect it.
SeqScans tend to get slow after a certain table size anyway so it's almost always going to be better to have an index at that point.
At 8:45 you mention the inverse of 99.99 and imply that it is -99.99, but in many cases the inverse of a number n is n raised to the negative 1 power, or 1/99.99 in this example.
You are correct, I used the term incorrectly here! My bad.
You can definitely store currency as a float if your primary objective is to siphon the difference between the real world and float value to your hidden account and then to Bahamas :)
Nice vid
love the db video, pls make more; liked and subscribed!(i never do that 😅)
can the `check` be easily updated? like adding a new currency???
Numeric is probably the correct solution. You can probably assume that monetary values won’t end up in high performance applications so having a hardware implementation doesn’t really matter. With unbounded Numerics, there could be massive performance pitfalls when using arithmetic like division and functions producing irrational numbers (depending on the implementation, compound interest with fractional years can already result in irrational numbers internally).
nice. thank you
One of the reasons why banks don't use floating-point numbers, is that it is possible to bypass floating-point numbers and round them up, hence the significant risk that someone could play around with them and increase their balance by using a few small fractions with numerous operations
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.
As I recall all values in SQLite are actually just some form of CHAR.
Did it really need another table for the currency? Since it's not a variable that changes a lot, if you hard code it, you can increase the speed of searching in the database, am I wrong?
For a many to one relationship then it's best practice to use another table.
In this case, we're able to store multiple currency price combinations for a course, which is useful if we're selling in different regions, such as EU, UK, USA etc.
Either way however, the best approach to increase searching performance would be to use an index. You could do this on the actually currency code as well.
i just use a boolean representing wether or not this person is in depth. I thought that was enough, but this video makes me rethink that decision
any recommendation for managing database universally using cli?
create a full playlist to improve productivity of software developer tools :)
what are the problems of using varchar and casting?
High quality content
The numeric is called Decimal in mysql.
please explain more about why did you choose varchar + check instead of enum
Often when I dev when I need a price for a ressource I use Stripe and they use the smallest currency unit for the `Charge object` so, `int` is the often the way to go if I want to store the price.
I have used BigInts in the past which means I always have to convert to denominations apart from that it was pretty cool
Nice framework laptop :^)
16-bit decimal type similar to C# or convert to cents or pence?
I tend to use (big)integers because it's usually easier to represent in whatever language I'm using and do arithmetic with. Numeric while great on paper (or if you exclusively do your arithmetic inside postgres) usually doesn't have a native equivalent in whatever language I'm using so I'm back to either dealing with it as a string, -or worse: the DB driver casts it to a float anyway.- Integers are indeed a pain when you suddenly end up needing to support fractions of a cent though.
> the DB driver casts it to a float anyway
That would defeat the purpose of having a numeric/decimal type in the first place and simply not what happens when using e.g. Django ORM on top of Postgres. Postgres numeric types are accurately converted to Python's Decimal as long as it fits the target representation.
@@squishy-tomato I'm not saying every ORM does this but some (especially in JS land) do this, or at least have an option to do this, and that's obviously not helpful when you want 100% accuracy.
EDIT: I took a look at current JS ORMs looks like they nowadays return strings for this, the behavior I'm referring to was an old version of sequelize /w the mysql2 driver. So It's not really an issue anymore
Varchar constrained with CHECK instead of enum? Can someone help me understand this? An Enum seems strictly better because as I understand they can do all the same things but just take less memory
It's a great question! I'm glad you asked it.
Enums are a good option for the use case of a currency code, although they do have a couple of caveats. 1. They're not standard SQL and they also can't have an enum value removed.
Personally, I tend to opt for a check constraint first as they're a bit more flexible, but that's just a personal preference!
I like using INTs representing cents because Go doesn't have a decimal primitive.
I should have known this a long time ago, now I'm using money type 😂
Small note: the code at 4:25 would fail with _any_ non-integer data type, since "is" compares the identity (memory address) of the two objects. Otherwise fantastic video!
I need to brush up my pythonic skills!
The only option I could see better than numerics would be creating a custom type that merges the numeric amount with the currency code, however, that would imply in having to define custom operations (add, sub, etc) that handle the different currency codes (e.g. if currencies are different, throw).
It can prevent some analytical bugs when doing stuff like `sum(transactions.value)` to get a user's balance, but on a service level it shouldn't make that big of a difference as this should be something you would be checking on your code anyways.
This is a very interesting point! It would help prevent any additions of currencies that shouldn't be added together.
In the CoursePrice table I added you could also group by currency when performing a sum so you could get the total for each currency as well, but yeah you could also make a mistake here easily.
@@dreamsofcode This is called Fowler's Money pattern and is very common. In fact, it also deals with cases with zero-decimal currencies (i.e. Japanese Yen), or have more than two decimals like Dinar, and it would make it more trivial to deal with changes to currency units.
Franework laptop reference nice
Bro 2:05 killed me.
Time to grind out some php :>
Make that money!
In VisiCalc we used scaled decimal values. It is floating point, but the artifacts correspond to what accounts are used to. Alas, all other spreadsheets went with floating point because, apparently, no one cared enough.
Separately, for currency, why not link to a separate currency table rather than wiring in the values?
By using internationalised 3 char codes for currency you're effectively providing an ID that you could later easily set up a lookup table to dereference later if required. Depending on the database you could even get it to store the values efficiently using some form of compression that resolves the the text values from an underlying identifier/enumeration.
How would you represent money in golang? pg just returns []byte for NUMERIC types.
Cast it in the SQL query to the base currency unit you want and handle it as an integer in Go
what do you think about using mariadb? it is great competitor to PostgreSQL. You can make a video about mariadb vs posgresql
I need to play with MariaDB some more! I believe it supports sharding which is a pretty interesting topic! I will add a video on to my backlog :)
@@dreamsofcode I can't wait to see the video :D
If you're gonna store currency information anyway, it would be better to go the whole hog and have a reference table for currencies. That way, your course prices that are all in the same currency are actually referencing the same currency record, instead of some magic strings that may or may not be literally identical, for instance if your DB by default isn't case sensitive, then 'usd' is as valid a value as 'USD', even though when you try to compare them in code, they're not the same. Plus, if you at some point decide to switch from the currency codes you were using to some other format, you can do so without having to change any real data, you can simply update the handful of records in the reference table.
(And before anyone says "But you'll never change the currency codes", just trust me, dumb managers and executives who don't know what they're talking about will at some point ask you to do something unbelievably short-sighted like change out the currency codes)
bigints are ints in a bigger font. I'm learning
I've come across a scary amount of applications that use a float to represent money.
They wonder why they have accounting errors.
Why is not a good idea to use ENUM?
easy approach is to keep arithmetic outside of the db :-)
The exact same issue also exists outside the db, except that you don't even have a MONEY type to ignore there.
@@ultru3525 very true, but you'll have much greater programmatic flexibility to handle the arithmetic, easier unit testing and you wont need to rebuild your logic if you need to change data stores in future
I like bigint using the smallest unit and the currency code. Big enough for the Vietnamese Dong.
I used to use NULL for currency
Many notable payment gateways advocates integers as the minor currency unit, the fact that its an industry standard make every other arguments purely academic.
For APIs yes, not for storing in a database.
That's because JSON will parse to a floating point number which is invalid. So an integer is easier.
Most postgres resources will tell you to store as numeric.
@@dreamsofcode I am guilty of being paranoid enough to store 2 more fractional digits after minor units, but the fact that not even the finance sector exchanges at this level of granuarity means probably most of us overengineered.
As some one that works with finance and postgres every day this is a pretty naive look at the subject. Depending on the context floats can be the right answer, ints are really never the answer and are a giant trap since you are missing a key but if information. Numeric are also not the slam dunk the video makes then out to be especially if you work in a language that doesn't have an equivalent type.
👍👍
I'm sure there's at least 1 bank which uses excel for as its database
I wish Sqlite supported NUMERIC type. 😢
phpeople mentioned
People like PHPeople!
Store numbers as images.
What is the point of having a money type if you cannot even have multiple currencies? That sounds like the bare minimum it should be able to do
That sounded like a terrible "feature" indeed. I don't know why anyone would use a currency type that could change with a database setting, it seems flawed by design.
SQLite Text: Hi~.
y not use double?
Double is a big float, so exactly the same issues arise
Quite silly of you to think that PHP developers care about small costs introduced by database storage.
PHP devs don't care, they're too busy spending their millions!
:)
and yet another reason to be a front end