@tnfssc
I use emojis to represent currency
@ultru3525
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.
@kazwalker764
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.
@potatomaaan1757
Since im not a PHP developer, I've thankfully never had to worry about this sort of stuff before
@medabottpro9124
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.
@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.
@hakuna_matata_hakuna
do a 10 minute postgres rash course am loving this
@wlockuz4467
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!
@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.
@jeeukko
I have a table with all possible int values inserted and just use the row id /s
@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.
@srki22
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.
@mahammadisgandarli4397
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.
@rodjenihm
I wouldn't be surprised if there is Postgres extension that handles currency as well.
@herozero777
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.
@benwilson5546
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.
@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.
@zaafhachemrachid1701
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
@Septumsempra8818
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? 🇿🇼🇿🇼🇿🇦🇿🇦
@dreamsofcode