Thread: Money type todos?
Hello, The money type is considered deprecated. I was also under the impression it would be eventually removed. Why are we accumulating TODOs for it? # -Make 64-bit version of the MONEY data type # Add locale-aware MONEY type, and support multiple currencies http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> The money type is considered deprecated. I was also under the impression
> it would be eventually removed. Why are we accumulating TODOs for it?
Because doing the TODOs would remove the reasons for deprecating it.
Whether it is actually ever going to disappear is not agreed upon.
regards, tom lane
On Tue, 20 Mar 2007 11:24:00 -0700 "Joshua D. Drake" <jd@commandprompt.com> wrote: > The money type is considered deprecated. I was also under the impression > it would be eventually removed. Why are we accumulating TODOs for it? > > # -Make 64-bit version of the MONEY data type Actually, this TODO is DONE. It's in HEAD now. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
D'Arcy J.M. Cain wrote: > On Tue, 20 Mar 2007 11:24:00 -0700 > "Joshua D. Drake" <jd@commandprompt.com> wrote: > >> # -Make 64-bit version of the MONEY data type >> > > Actually, this TODO is DONE. It's in HEAD now. > That is what the "-" prefix denotes. -Neil
Tom Lane skrev: >> The money type is considered deprecated. I was also under the impression >> it would be eventually removed. Why are we accumulating TODOs for it? > > Because doing the TODOs would remove the reasons for deprecating it. > > Whether it is actually ever going to disappear is not agreed upon. What is the reason to keep it? The arguments I've seen is that numeric is too slow to use when you have a lot of money calculations to perform. But with that argument we should instead make a general artitmetic type that is fast and useful to more things than just money. Just drop the currency from money and we have one such type. Would we accept other money-like types, with other units? Like kilogram, liter, yards, square meters, and so on? And whatuse is the unit in money? It's not like it will do currency conversion or anything like that. I think money should go away and the database should provide more general types. /Dennis
Dennis Bjorklund <db@zigo.dhs.org> writes:
> Tom Lane skrev:
>> Whether it is actually ever going to disappear is not agreed upon.
> What is the reason to keep it?
The words-of-one-syllable answer is that D'Arcy Cain is still willing
to put work into supporting the money type, and if it still gets the
job done for him then it probably gets the job done for some other
people too.
Personally, as a former currency trader I've not seen any proposals on
this list for a "money" type that I'd consider 100% feature complete.
The unit-identification part of it is interesting, but pales into
insignificance compared to the problem that the unit values vary
constantly; what's more, that variance is not to be swept under the rug
but is exactly the data that you are interested in. Next, the units
themselves change from time to time (euro? what's that?); next, the
interconversion rates aren't all exactly equivalent, and that's not
noise either but rather very interesting data (see "arbitrage").
So I'm not feeling inclined to try to prescribe that datatype X is
good while datatype Y is bad. It's more about whether there's an
audience for any particular datatype definition. The present money
code gets the job done for D'Arcy and probably some other people,
and we see some straightforward ways to improve it to serve some
more cases, so what's wrong with pursuing that path?
regards, tom lane
> Dennis Bjorklund <db@zigo.dhs.org> writes: >> What is the reason to keep it? > > The words-of-one-syllable answer is that D'Arcy Cain is still willing > to put work into supporting the money type, and if it still gets the > job done for him then it probably gets the job done for some other > people too. > > Personally, as a former currency trader I've not seen any proposals on > this list for a "money" type that I'd consider 100% feature complete. > The unit-identification part of it is interesting, but pales into > insignificance compared to the problem that the unit values vary > constantly The unit (currency) part is what I don't like about the money type. To have a fast and size limited fixed point type is something I think is good. It could very well be called money if we want to or we can give it a more neutral name. /Dennis
Tom Lane wrote: > Dennis Bjorklund <db@zigo.dhs.org> writes: >> Tom Lane skrev: >>> Whether it is actually ever going to disappear is not agreed upon. > >> What is the reason to keep it? > > The words-of-one-syllable answer is that D'Arcy Cain is still willing > to put work into supporting the money type, and if it still gets the > job done for him then it probably gets the job done for some other > people too. > > Personally, as a former currency trader I've not seen any proposals on > this list for a "money" type that I'd consider 100% feature complete. > The unit-identification part of it is interesting, but pales into > insignificance compared to the problem that the unit values vary > constantly; what's more, that variance is not to be swept under the rug > but is exactly the data that you are interested in. Next, the units > themselves change from time to time (euro? what's that?); next, the > interconversion rates aren't all exactly equivalent, and that's not > noise either but rather very interesting data (see "arbitrage"). > > So I'm not feeling inclined to try to prescribe that datatype X is > good while datatype Y is bad. It's more about whether there's an > audience for any particular datatype definition. The present money > code gets the job done for D'Arcy and probably some other people, > and we see some straightforward ways to improve it to serve some > more cases, so what's wrong with pursuing that path? > > regards, tom lane Agreed with Tom on this one. Full usage of money is beyond tagged types etc. For example, when you earn money in another currency, it is the time at which you earn it that describes its value. So for P&L accounts there is generally no change in exchange rates over time and you need to track what the rate was at time of earning. Solution is to date earnings and have a table of exchange rates by day. For balance sheet accounts, their value at a given point in time in a home currency is of course dependent on exchange rates which creates the currency gain or loss on the P&L side, the account that captures exchange rate movements. But this is dependent on the relative differences between the rates when every dollar was earned and current rates. Darcy had suggested removing the currency symbol. That is a change I'd support. The only other nice thing would be user defined precision, but can live without that as most currencies work under nnn.mm. Speed is everything in these systems. For a complex general system you often can get away with integers if you define at the app layer the handling (including a lookup in system for format, type). - August
August Zajonc wrote: > The only other nice thing would be user defined precision, but > can live without that as most currencies work under nnn.mm. That's useless for our system at least. The minimum scale we use for money values is 5. I guess we can just continue to use numeric though. cheers andrew
On Wed, 21 Mar 2007 02:31:44 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dennis Bjorklund <db@zigo.dhs.org> writes: > > Tom Lane skrev: > >> Whether it is actually ever going to disappear is not agreed upon. > > > What is the reason to keep it? > > The words-of-one-syllable answer is that D'Arcy Cain is still willing > to put work into supporting the money type, and if it still gets the > job done for him then it probably gets the job done for some other > people too. My testing suggests that the money type is faster for certain tasks involving internal calculations usually and slower on others such as I/O. In fact I would like to find out what NUMERIC does to get its speed and see if MONEY can use that. > So I'm not feeling inclined to try to prescribe that datatype X is > good while datatype Y is bad. It's more about whether there's an > audience for any particular datatype definition. The present money > code gets the job done for D'Arcy and probably some other people, > and we see some straightforward ways to improve it to serve some > more cases, so what's wrong with pursuing that path? I still get the odd message from people telling me that they hope it stays in. I suspect that the main reason that more people don't use it is that we keep saying that it is going away. Perhaps we should either put forward an actual schedule for removing it or stop telling people that it is deprecated. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Wed, 21 Mar 2007 02:13:54 -0700 August Zajonc <augustz@augustz.com> wrote: > Agreed with Tom on this one. Full usage of money is beyond tagged types > etc. For example, when you earn money in another currency, it is the > time at which you earn it that describes its value. So for P&L accounts > there is generally no change in exchange rates over time and you need to > track what the rate was at time of earning. Solution is to date earnings > and have a table of exchange rates by day. Or hour or minute or second depending on the business rules. This is one of the reasons that I don't see currency information being embedded too deeply into the type beyond simple tagging. I find that when I need to work with exchange rates that I am better off just storing the original amount in one field for display purposes and the exchanged amount based on the exact time of the transaction in another. Even tagging the type gives me pause. I would want to do some testing to see if checking the tag slows down calculations. All of the suggested functionality of tagged types can be done with extra fields and rules anyway so you can get whatever your business rules dictate without it. > For balance sheet accounts, their value at a given point in time in a > home currency is of course dependent on exchange rates which creates the > currency gain or loss on the P&L side, the account that captures > exchange rate movements. But this is dependent on the relative > differences between the rates when every dollar was earned and current > rates. Exactly the sort of complication that I don't think belongs in the core database. These sorts of things need to be in contrib. > Darcy had suggested removing the currency symbol. That is a change I'd In fact, that was in my original patch when the type was widened. I only took it out of the proposal because it muddied the waters and made for too many (i.e. > 1) big changes in one patch. I am still willing to remove it now as a separate patch if that is the concensus. > support. The only other nice thing would be user defined precision, but > can live without that as most currencies work under nnn.mm. Speed is This is probably the single biggest thing keeping the type from being more widely accepted since it enforces a specific business rule on the type albeit a very ubiquitous one. > everything in these systems. For a complex general system you often can > get away with integers if you define at the app layer the handling > (including a lookup in system for format, type). Sometimes a "psql -c" is all you need to get useful information. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Andrew Dunstan wrote: > August Zajonc wrote: >> The only other nice thing would be user defined precision, but >> can live without that as most currencies work under nnn.mm. > > That's useless for our system at least. The minimum scale we use for > money values is 5. I guess we can just continue to use numeric though. > Emailing off list with Andrew I'm reminded of one other feature that would be potentially valuable in a money type, and that's being able to specify the method of rounding. There end up being a number of different rounding methods out there, and in some cases it is very valuable to critical to follow a certain method, usually dictated by externalities such as trading partners etc. This could probably be a compile time flag even for postgresql and is obviously a low priority behind speed. Symmetric Arithmetic Rounding Bankers Rounding Round-half-down Not sure its worth doing and if so probably a low priority, just mentioning it for completeness. - August
August Zajonc wrote: > Agreed with Tom on this one. Full usage of money is beyond tagged types > etc. For example, when you earn money in another currency, it is the > time at which you earn it that describes its value. So for P&L accounts > there is generally no change in exchange rates over time and you need to > track what the rate was at time of earning. Solution is to date earnings > and have a table of exchange rates by day. Personally I think a true money type should hold the numeric value and optionally the currency (similar to the timestamp with timezone) and have support functions that handle the i/0 conversion (text - $US1,000.00 - to money) as happens now. As opposed to the db designer storing it in different columns. But I think the data returned should be of purely numeric type unless a function is used to get pretty currency formatting or requesting the currency. > For balance sheet accounts, their value at a given point in time in a > home currency is of course dependent on exchange rates which creates the > currency gain or loss on the P&L side, the account that captures > exchange rate movements. But this is dependent on the relative > differences between the rates when every dollar was earned and current > rates. Well the exchange rate at the time the payment is received is only the speculative (possible) value of a foreign currency and may not account for exchange fees either. This speculative value changes daily (hourly) and is not really relevant to the money amount recorded. The speculative value is only relevant at the time a report is run to show current value in a common currency. If you have bank accounts in different countries then the exchange rate at the time of running, say a balance sheet, will give you the speculative value in a common currency of your foreign bank accounts. The true value to you will only be realised when you transfer the foreign money to your local account and get $xx affecting your local account balance after exchange fees using the exchange rate on offer at the time you initiate the exchange. > Darcy had suggested removing the currency symbol. That is a change I'd > support. The only other nice thing would be user defined precision, but > can live without that as most currencies work under nnn.mm. Speed is > everything in these systems. For a complex general system you often can > get away with integers if you define at the app layer the handling > (including a lookup in system for format, type). As I mentioned before I think the returned data should be pure numeric value unless requesting formatted data. I also agree with the precision setting, saving only ddd.cc is not universal enough for all applications. Some industries work with tenth's or hundredth's of a cent for their pricing and calculations and are only rounded to a whole cent on the final invoice when payment is due. Not sure about America but here in Australia petrol is sold at xxx.x cents per litre with the total being rounded to a whole cent when payment is made. And our smallest coin is 5 cents so cash paying customers are also rounded to 5 cent increments. -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz
Shane Ambler wrote: > August Zajonc wrote: > >> Agreed with Tom on this one. Full usage of money is beyond tagged types >> etc. For example, when you earn money in another currency, it is the >> time at which you earn it that describes its value. So for P&L accounts >> there is generally no change in exchange rates over time and you need to >> track what the rate was at time of earning. Solution is to date earnings >> and have a table of exchange rates by day. > > > Personally I think a true money type should hold the numeric value and > optionally the currency (similar to the timestamp with timezone) and > have support functions that handle the i/0 conversion (text - > $US1,000.00 - to money) as happens now. As opposed to the db designer > storing it in different columns. It'd be nice if there were an easy, standard solution to this problem- but I don't think there is. For example, our application (which would be greatly simplified if there was a standard solution to this) knows of ~200 different currencies, including such standards as the Uganda Shilling, the Zambia Kwacha, and Ethiopian Birr. Not to mention you get situations where goverments (for various reasons) issue "new" currency, like the "new" Zimbabwe Dollar, vr.s the "old" Zimbabwe Dollar. Confuse these two and you'll lose your shirt. Personally, I don't think it's that big of a deal to have to do in my queries: SELECT table.amount || ccy.code FROM table LEFT JOIN lu_currency AS ccy WHERE table.ccy_id= ccy.id to make the report come out as "1000000USD". Brian
Shane Ambler wrote: > August Zajonc wrote: >> For balance sheet accounts, their value at a given point in time in a >> home currency is of course dependent on exchange rates which creates the >> currency gain or loss on the P&L side, the account that captures >> exchange rate movements. But this is dependent on the relative >> differences between the rates when every dollar was earned and current >> rates. > > Well the exchange rate at the time the payment is received is only the > speculative (possible) value of a foreign currency and may not account > for exchange fees either. This speculative value changes daily > (hourly) and is not really relevant to the money amount recorded. The > speculative value is only relevant at the time a report is run to show > current value in a common currency. Statement 8 of the FASB. "This Statement requires that all amounts measured in a foreign currency be translated at the exchange rate in effect at the date at which the foreign currency transaction was measured. All exchange gains and losses were required to be included in income in the period in which they arose, i.e., when the rates changed." In other words, for companies accounting according to FASB the exchange rate at the time revenue is recognized is *critically* important. And it turns out there are good reasons for this treatment, as the decision to hold revenue n foreign currency is a speculative one ultimately and belongs in currency gain / (loss). Multiple currency handling is both a complicated area, but one with some relatively well defined approaches. Saying that the exchange rate at the time of recognition is not relevant seems a stretch, I'd be curious what systems you are using this approach for of course :) And why would you include exchange rate fees in a non-cash transaction? Those fees are generally accounted for differently, and reduced by making large transactions (which again argues for handling them separately as they are dependent not on recognition of revenue but on method of conversion at a later date). Sorry, but I've head a lot of comments on the money types that make me very curious as to how people are actually using them! Would love to learn more as it is a space I am interested in and have some experience with! - August