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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)druid(dot)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(at)commandprompt(dot)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 what use 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(at)zigo(dot)dhs(dot)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(at)zigo(dot)dhs(dot)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(at)zigo(dot)dhs(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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(at)druid(dot)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(at)augustz(dot)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(at)druid(dot)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(at)Sheeky(dot)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