Re: Money type todos? - Mailing list pgsql-hackers

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.