Common mistakes in PostgreSQL

Re:Common mistakes in PostgreSQL by IgorPartola on Friday May 03, 2019 @10:36AM (#19818822)

Money is always funny to represent. I did a bunch of research on this for my budgeting app. Turns out that while most countries use two decimal points for their cent/cent equivalent, some use 0, 1, or 3. In addition, at least one country (Sweden I think), doesn't allow values like 0.01. Their smallest denomination is 0.05, and everything is a multiple of it. I ended up storing the data as a decimal type with 3 digits after the decimal, along with the currency. Then I periodically extract the locale data from a Linux timezones package and the app has a giant lookup table for how to represent and parse each currency + value combo. This changes from time to time as currencies get updated and governments change.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by rb808 on Friday May 03, 2019 @11:16AM (#19819151)

> In addition, at least one country (Sweden I think), doesn't allow values like 0.01.

There are a few countries where they no longer bother with small coins to represent .01, so cash transactions are rounded to nearest .05 or similar, but the .01 is still valid for electronic transactions. I expect that is the situation in Sweden. (I wish US did this too)

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by Mister_Snuggles on Friday May 03, 2019 @03:21PM (#19821416)

Canada did this. No more $0.01 coins, $0.05 is the smallest denomination.

Electronic transactions, which account for over 50% of transactions in Canada, still use pennies though.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by manojlds on Friday May 03, 2019 @11:18PM (#19824200)

India got rid of 0.1, 0.25 and 0.5. Cash transactions are rounded to the rupee.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by kentosi on Friday May 03, 2019 @01:37PM (#19820451)

And Australia. We got rid of 1c and 2c coins back in the 90s.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by axman6 on Sunday May 05, 2019 @12:58AM (#19831252)

But electronic transactions are still processed to the cent.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by axman6 on Sunday May 05, 2019 @01:17AM (#19831305)

The Haskell library safe-money does a great job of handling these weird cases for currencies. It has a separation between the types used for calculations and storage/presentation, and lets you define arbitrary ones so in your example you can handle computations in an arbitrary precision rational representation and then store it in cents and later convert with proper rounding to 5 cent increments if necessary. Much of this is handled by the type system, including type safe currency conversion.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by daxterspeed on Friday May 03, 2019 @05:02PM (#19822248)

All electronic transactions handle the SEK cent (1/100 SEK aka öre) though there are no longer any valid SEK cent coins. The 50 cent coin was taken out of circulation September 30th 2010 and nowadays all remaining cent coins are considered scrap metal.

There's (sadly) no rule that everything must be a denomination of 0.05 SEK or even 1 SEK. Pretty much every shop continues to use cents in their pricing (like .50, .95, and .99). If you pay in cash you'll end up rounding to nearest SEK (rounding up if it's below 1 SEK). You could potentially save money by paying with cash if your transaction would be rounded down... but at a maximum of 0.49 SEK (~0.05 USD) saved per transaction you'd need some unique spending habits to actually utilize this "trick" efficiently.

Source: https://www.riksbank.se/en-gb/payments--cash/notes--coins/co... and I happen to be Swedish.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by emptyfile on Friday May 03, 2019 @11:11AM (#19819099)

As someone who does a lot of work in payroll (in eastern europe...) the swedish system sounds like a dream.

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by paulddraper on Friday May 03, 2019 @01:05PM (#19820161)

Why?

[ Parent | on HN ]

Re:Common mistakes in PostgreSQL by bloemy on Friday May 03, 2019 @06:13PM (#19822736)

Switzerland only has 0.05 as a minimum denomination too, might be the one you were thinking about.

[ Parent | on HN ]