Re: Money casting too liberal? - Mailing list pgsql-general

On 30/03/13 11:30, Gavan Schneider wrote:
> On 29/3/13 at 3:32 AM, D'Arcy J.M. Cain wrote:
>
>> On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote:
>>> Well, this has been discussed before, and the majority view every
>>> time has been that MONEY is a legacy thing that most people would
>>> rather rip out than sink a large amount of additional effort into.
>
> The only reason I have tried to explore these ideas is that the type
> is currently too quirky for most use cases. So I must agree that
> remove/ignore is the least work option. An argument for making the
> type more useful can be made by analogy to the geolocation add-in
> type. Most never go there but those who need to do so seem to prefer
> the builtin functionality over hand coding the same behaviour with
> columns of arrays that just happen to contain location data.
>
>>> It has some use-cases but they are narrow, and it's not clear how
>>> much wider the use-cases would be if we tried to generalize it.
>
> A well designed and specific tool can be worth the effort.
>
> The use cases include:
>
>     Financial data, accounts in a single currency, i.e., the money
> column in a transaction
>
>     Multi currency data, i.e., keeping track of transactions across
> several currencies.
>         specifically we are NOT doing conversions, what arrives/leaves
> as $ or ¥ stays that way,
>         this implies the dB has tables for each area of operation or
> columns for each currency
>
> One thing the type should not attempt or allow any implicit
> transforming of alues. Mostly a currency change is a transaction and
> whenever it happens it has to be recored as such, e.g., so many ¥
> leave their column, appropriate $ are added to their column, and
> commission $/¥ is added to its column, also included will be: exchange
> rate reference time-stamp journal reference, etc. A constraint could
> be constructed to ensure the double entry book keeping zero sum
> convention has been maintained across the whole transaction.
>
> One time this might not be so detailed is for a VIEW where something
> akin to total worth is being reported. In cases like this the exchange
> rates would usually be in their table and the business rules would
> dictate which one is to be used to build the VIEW, e.g., end of month
> report, and it might be shown with all values in a single currency
> depending on the company's HQ.
>
>
>> I wonder if our vision isn't a little tunneled here.  Using this type
>> for money is, perhaps, a specialized use and the type should really be
>> called something else and modified to remove all connotations of money
>> from it.  So...
>>
>
>> - Drop the currency symbol
>> - Allow number of decimals to be defined once for the column
>> - Don't use locale except to specify decimal separator (',' vs. '.')
>>
> Mostly this is cosmetic and only relevant for parsing text on data
> entry or default formatting with SELECT on the command line. The power
> of the class is that none of this is in the data other than as dB
> column flags. The values themselves are integer. The class is meant to
> keep the books moving right along.
>
>> - Allow operations against numeric
>>
> Whatever else is done this should happen.
>
>> Not sure what to rename it to.  Decimal would be good if it wasn't
>> already in use.  Maybe DecimalInt.
>>
> I don't think there is much use for another fixed precision integral
> type. NUMERIC does a good job when INTEGER isn't suitable. If this
> exercise is worth anything then MONEY should just do its job better so
> people who track money (and there is an awful lot of them) will find
> it useful.
>
>>> My own experience with this sort of thing leads me to think that
>>> real applications dealing with a variety of currencies will be
>>> needing to store additional details, such as the exact exchange
>>> rate that applied to a particular transaction.  So while merely
>>
>> Seems like something that can be stored in a different column.
>>
> Exactly. We to think this through as would a real user.
>
> If the business is receiving money from multiple regions then there
> will be rows which show the currency, number of units (numeric type
> since the column is not devoted to a specific currency), transaction
> tracing data, exchange reference (another table),
> amt_received::MONEY('USD','D2'),
> amt_transaction_fee::MONEY('USD','D3'), etc.
>
> Within the accounts of the organisation the MONEY columns are likely
> to be in a single currency with movements between ledgers in the time
> honoured fashion of adding to this while removing the same from
> other(s) so all money entries add to zero across the row. Movements
> between currencies are just another transaction as detailed above.
>
> I have sketched something of a notation for MONEY columns along these
> lines:
>
>     amt_received MONEY (    CURRENCY    -- e.g., 'USD' 'AUD' 'YEN' ...
>                             [,SCALE     -- default as per currency,
> e.g. USD 2 decimals
>                                         -- but could be used to see
> money in bigger units
>                                         -- such as '000s (e.g., that
> end-of-month view)
>                             [,ROUND     -- need to allow for multiple
> rules here, sometimes
>                                         -- cents are just dropped,
> otherwise it can be
>                                         -- required that rounding is
> up or down
>                             [,OTHER?
>                         ]]])
>
> I have left the display characteristics out (they could be there as a
> default) but column values are going to be displayed however the
> application wants them, and this only applies at the time of
> reporting. Each currency can carry the conventional defaults and the
> application should have formatting tools to alter this during output.
>
> Inputting money values, i.e., text to MONEY should follow the
> conventions of the target currency. Specifically the input conversion
> routine should handle the symbol (or no symbol) and all the usual
> conventions for negative values, decimals and separators. It should
> throw an error if asked to add a value to a USD column but finds a yen
> symbol in the text. (There is no such help for all of us sharing the $
> symbol. :) Also it should parse such things as 123.456,00 (Europe) and
> 123,456.00 (Anglo) properly. Errors need to be thrown when it looks
> wrong 123,456.789.00 -- since this is likely to be corrupted data, and
> finally gets me back to the issue raised by OP. :)
>
> Hope this hasn't been too much of a ramble.
>
> Regards, and happy (Western) Easter to all,
> Gavan Schneider
>
>
>
While for my current project I don't need this, I've dealt with money
many times, so it would be great to have a proper money type when I next
needed to handle money in a database (which is bound to happen sooner or
later!)!

How about a picture clause for display - encodes details about how to
handle negative numbers.

QUESTION:
How best to handle the difference between English and European
conventions for thousand separators:
  English convention: NZ$1,000,005.34
European convention: NZ$1.000.005,34
note the use of '.' & ',' are swapped!

QUESTION:
Should the type of $ (NZ$ vs US$) be dropped if it matches the locale?


Cheers,
Gavin