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

@dreamsofcode

Ай бұрын

I love this idea so much.

@Elesario

Ай бұрын

While I wish my account was 🤑 It's more like 😭

@garretmh

Ай бұрын

I demand emojis for all ISO 4217 currency codes. Not just💵, 💶, 💷 and 💴

@efraim6960

Ай бұрын

🍎= 1000

@BradenJohnYoung

Ай бұрын

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.

@landix537

Ай бұрын

Send it to him

@follantic

Ай бұрын

Using floats (with decimals) for money is never a good idea.

@aldogutierrez8240

Ай бұрын

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. 🤣🤣

@__Brandon__

Ай бұрын

Use bool instead

@Vichion

Ай бұрын

@@__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.

@funkdefied1

Ай бұрын

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.

@Serizon_

Ай бұрын

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

@JoseHenrique-xg1lp

Ай бұрын

it is very sophisticated

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

@dreamsofcode

Ай бұрын

One of the perks of not having millions of dollars.

@samjiman

Ай бұрын

You'll never get that Lambo then, sonny 😂

@TarasShabatin

Ай бұрын

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.

@srki22

Ай бұрын

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.

@medabottpro9124

Ай бұрын

@@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

@srki22

Ай бұрын

@@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.

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

@dreamsofcode

Ай бұрын

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? 🇿🇼🇿🇼🇿🇦🇿🇦

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 wouldn't be surprised if there is Postgres extension that handles currency as well.

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.

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.

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.

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 :)

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!

@dreamsofcode

Ай бұрын

Having a separate table is also a really good choice!

@MelroyvandenBerg

Ай бұрын

Yes

@adrianbool4568

Ай бұрын

@@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!

Very well explained. Thanks for sharing!

Your videos are just great. Thanks!

Just now I subscribed to your channel and got notification. Your video on nvim helped me a lot. ❤️

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.

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.

Awesome video as usual!

Very useful and informative video. Thanks bro 😊

Loved your video and ohh the animation just lovely ❤️❤️

@dreamsofcode

Ай бұрын

Thank you!

great video! Really enjoyed it ❤

This is exactly the kind of video that I like

Great video, thank you 🙏

Awesome material!

Thank youuuu❤

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

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.

Thanks for teaching this!!! I thought this was obvious but the evidence shows that is not.

just what I needed

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.

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.

What's the advantage of using a CHECK statement instead of an ENUM?

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.

@dreamsofcode

Ай бұрын

You are correct, I used the term incorrectly here! My bad.

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.

nice. thank you

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

@dreamsofcode

Ай бұрын

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?

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

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?

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.

please explain more about why did you choose varchar + check instead of enum

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.

can the `check` be easily updated? like adding a new currency???

This guy is better than fireship

@CoolestPossibleName

Ай бұрын

No (⁠╯⁠°⁠□⁠°⁠）⁠╯⁠︵⁠ ⁠┻⁠━⁠┻

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.

any recommendation for managing database universally using cli?

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.

@Elesario

Ай бұрын

As I recall all values in SQLite are actually just some form of CHAR.

love the db video, pls make more; liked and subscribed!(i never do that 😅)

what are the problems of using varchar and casting?

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?

@dreamsofcode

Ай бұрын

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.

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

create a full playlist to improve productivity of software developer tools :)

Nice vid

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.

@squishy-tomato

Ай бұрын

> 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.

@SjoerdTheDev

Ай бұрын

@@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

I have used BigInts in the past which means I always have to convert to denominations apart from that it was pretty cool

I like using INTs representing cents because Go doesn't have a decimal primitive.

The numeric is called Decimal in mysql.

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.

@bepamungkas

9 күн бұрын

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.

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 :)

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

16-bit decimal type similar to C# or convert to cents or pence?

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).

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?

@dreamsofcode

Ай бұрын

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.

High quality content

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.

@dreamsofcode

Ай бұрын

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.

@dealloc

Ай бұрын

@@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.

What are the "caveats" of using ENUM types in postgres?

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

@dreamsofcode

Ай бұрын

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!

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?

@Elesario

Ай бұрын

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.

Franework laptop reference nice

I should have known this a long time ago, now I'm using money type 😂

Bro 2:05 killed me.

Nice framework laptop :^)

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!

@dreamsofcode

Ай бұрын

I need to brush up my pythonic skills!

bigints are ints in a bigger font. I'm learning

How would you represent money in golang? pg just returns []byte for NUMERIC types.

@dreamsofcode

Ай бұрын

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

@dreamsofcode

Ай бұрын

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 :)

@marko95g

Ай бұрын

@@dreamsofcode I can't wait to see the video :D

👍👍

Why is not a good idea to use ENUM?

Time to grind out some php :>

@dreamsofcode

Ай бұрын

Make that money!

I've come across a scary amount of applications that use a float to represent money. They wonder why they have accounting errors.

I used to use NULL for currency

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)

I like bigint using the smallest unit and the currency code. Big enough for the Vietnamese Dong.

easy approach is to keep arithmetic outside of the db :-)

@ultru3525

Ай бұрын

The exact same issue also exists outside the db, except that you don't even have a MONEY type to ignore there.

@danstephenson4940

Ай бұрын

@@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

Many notable payment gateways advocates integers as the minor currency unit, the fact that its an industry standard make every other arguments purely academic.

@dreamsofcode

Ай бұрын

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.

@_vicary

Ай бұрын

@@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 wish Sqlite supported NUMERIC type. 😢

I'm sure there's at least 1 bank which uses excel for as its database

phpeople mentioned

@dreamsofcode

Ай бұрын

People like PHPeople!

y not use double?

@theunskruger1211

23 күн бұрын

Double is a big float, so exactly the same issues arise

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

@squishy-tomato

Ай бұрын

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.

Store numbers as images.

SQLite Text: Hi~.

Quite silly of you to think that PHP developers care about small costs introduced by database storage.

@dreamsofcode

Ай бұрын

PHP devs don't care, they're too busy spending their millions!

As a PHP developer, i create 2 tables called monetary_amounts_[whole|fractional] and dynamically add a column per digit required in either on the fly, so as to make the application scalable. Each row has an FK for the other and the currency as well. My php code to return and evaluate a monetary value thus requires n number of times more computational cycles depending on the number, which, again, as a PHP developer, is a hidden time tax on only the most wealthy users on my application.