The best (and worst) types for storing money in PostgreSQL
Vložit
- čas přidán 29. 04. 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 - Věda a technologie
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.
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
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.
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.
do a 10 minute postgres rash course am loving this
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 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.
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.
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 :)
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.
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?
🇿🇼🇿🇼🇿🇦🇿🇦
Your videos are just great. Thanks!
Very well explained. Thanks for sharing!
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.
I wouldn't be surprised if there is Postgres extension that handles currency as well.
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!
Just now I subscribed to your channel and got notification.
Your video on nvim helped me a lot. ❤️
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.
Loved your video and ohh the animation just lovely ❤️❤️
Thank you!
Awesome video as usual!
This is exactly the kind of video that I like
Very useful and informative video. Thanks bro 😊
great video! Really enjoyed it ❤
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, thank you 🙏
Awesome material!
Great video! Thanks a lot!
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
just what I needed
SQL tips. Love it.
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.
Thanks for teaching this!!!
I thought this was obvious but the evidence shows that is not.
Thank youuuu❤
what a wonderful video
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.
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.
nice. thank you
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.
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.
This guy is better than fireship
No (╯°□°）╯︵ ┻━┻
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.
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
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?
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
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.
High quality content
Nice vid
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?
I have used BigInts in the past which means I always have to convert to denominations apart from that it was pretty cool
love the db video, pls make more; liked and subscribed!(i never do that 😅)
create a full playlist to improve productivity of software developer tools :)
What's the advantage of using a CHECK statement instead of an ENUM?
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.
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 :)
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?
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).
The numeric is called Decimal in mysql.
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.
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
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.
please explain more about why did you choose varchar + check instead of enum
I like using INTs representing cents because Go doesn't have a decimal primitive.
any recommendation for managing database universally using cli?
can the `check` be easily updated? like adding a new currency???
Nice framework laptop :^)
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.
If you use PHP (like any would-be-rich programmer did), either use older bcmath or newer decimal type, the db driver should handle conversion with zero error. Other languages also have either stdlib or third party decimal library, although for third party there's no hard guarantee of 1-1 conversion.
what are the problems of using varchar and casting?
16-bit decimal type similar to C# or convert to cents or pence?
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.
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!
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.
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.
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.
Time to grind out some php :>
Make that money!
What are the "caveats" of using ENUM types in postgres?
👍👍
I used to use NULL for currency
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.
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!
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)
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
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.
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
Why is not a good idea to use ENUM?
I'm sure there's at least 1 bank which uses excel for as its database
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 wish Sqlite supported NUMERIC type. 😢
phpeople mentioned
People like PHPeople!
Store numbers as images.
SQLite Text: Hi~.
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.
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!