Let's talk about money. Everybody loves money. Everybody likes money. We need money, we need to store money in the database. There is a type specifically called money, I'm going to beg you to not use it to store money in the database.

Let me show you this type real fast. It is simply money. If you write this you do a create table with an item name as text a price as money, then we can insert into this table some values. We can insert literal floats, literal fractional values. Let's go ahead do a literal integer there. You can also insert a currency formatted string. If we were to run this then we can get rid of it we'll do select * from money. I think it's just money example. Look, all of them went in just fine. The one, I forget which one, I think it was headphones, we inserted as a string, totally fine. This one we inserted as an integer, totally fine. The rest of them we inserted as fractional values.

We inserted floats or whatever, decimals. Those all went in just fine. What's the problem with money? Well, let's do another one. If we were to insert another one here. In fact, let's stop inserting let's just start casting, that's a little bit easier to see. If we did select 199::money, okay, that's fine, 99, 99 99,876, just you lose precision, right.

If you are working with US dollars you don't care about fractional cents, maybe this is good. Depends on your application, of course, you're not going to lose precision for two decimal places. The second that you need fractional cents or you're working in a currency or maybe like a cryptocurrency where you need a bunch of fractional parts, your host, you're just straight outta luck.

Let's talk about another thing. Let's do that again. Let's select 1000 cast as money, okay? That comes back with a nice beautiful dollar sign a comma for the thousands separator. Let's say you're storing everything. You're storing everything in your database this way. US dollars, it all depends on this lc monetary setting. We have our monetary setting set to EN US.UTF-8. That is this setting right here. If we set this again, nothing is gonna change. All of this is going to work just fine. That is what's driving, we can do it over here. That's what's driving select * from money example. All those dollar signs, that is from lc monetary. If we were to simply change this to GB UTF-8, everything has changed to pounds. Great British pound sterling, I guess. Everything has changed two pounds except the actual values, right?

It looks like these were all stored as great British pounds, in fact they were stored as US dollars then we changed a setting it's totally hosed. This is very confusing 'cause it didn't actually do a conversion, like it didn't do a currency conversion, it just changed the sign. That's problem number one. We don't want that. , problem number two, maybe these are outta order. Problem number two was the two decimal precision. We potentially don't want that.

We are guaranteed that operations with money will be precise. That is good. If you think back to just a few videos ago, we have two other options for precision. Floating point is out because it's not precise let's talk about those two other options instead. The first option that you have is to store everything as an integer in the lowest unit that you need to store. If you're trying to store dollars cents not fractional cents, well then you can multiply the value times 100. You could do it like this. If you were to say, I'm trying to store the value $100.78 cents, you could say, alright, in fact, as an integer to get that to an integer, I just need to multiply it by 100 then I can store then we can cast this. Let's cast this to an int four. I can store that as cents. That is a totally legit way to store money.

Should you decide based on your application that you do in fact need to store fractions of a penny, well, you can do this you can say, what would that be? I think that's thousandth of a cent. There you go. The burden has shifted a little bit. The burden of understanding the data has shifted a little bit because you do have to remember on the way in, you need to make sure that it's easier to reason about talk about cents.

On the way in you convert it to cents on the way out, depending on what you're trying to display, you convert it back to dollars cents. This is very reasonable. You may not like it, which is totally fine. This is the way that Stripe does their API. If you get something back from Stripe, or I think if you send it to Stripe, you have to send it in the lowest unit of that currency. That makes it very, very easy because integers are fast, they're accurate, they're easy to pass around, nobody's gonna accidentally muck it up with a floating point operation. It's an integer. That is totally legit.

Obviously the other option that you have is to just simply store it as a numeric. This becomes, we'll just call this money. You can just store it as a numeric you can set the precision scale to whatever you want. Here we've left it completely unbounded. You could say I need 10 total digits two decimal place digits. That's fine. You could say I need four decimal place digits that would put you out to 0.7800. Depending on what the scale precision are that you need, you can declare numeric that way.

Remember, numeric is slower as we demonstrated in the comparison against floating point , that may not matter. The thing that you care most about when it comes to money is precision. You can store the lowest common unit, I wanna say lowest common denominator. You can store the lowest unit in the currency that you need or you can do it as a numeric set the precision scale however you need. When it comes to storing multiple currencies, my recommendation would be have a separate column that stores the currency next to the value. If everything is always going to be USD, I don't think I would store it.

The second that you add USD Canadian dollars great British pounds, that's when I would start putting a currency identifier next to the value. I would not rely on money at all for storage. If you wanted to, you could store it as a numeric then just for giggles, you could cast it into a money.

If you need that sort of like help on the presentation, that's totally fine. I have no problem with that. I just don't want you storing the money as money, store it as an integer or store it as a numeric.