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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
