Thread: The Accountant is not Amused
Is this fixed in later versions? If not, should I send in a patch? -Michael Robinson ----------------------------- Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.1 on i386-unknown-freebsd3.3, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute queryYou are currentlyconnected to the database: template1 template1=> select 9.99::money * 0.1; ?column? -------- $0.99 (1 row) template1=> select 9.99::money / 10; ?column? -------- $0.99 (1 row) template1=> select 9.99::money / 10.0; ?column? -------- $1.00 (1 row) template1=>
hi. > Is this fixed in later versions? If not, should I send in a patch? > > -Michael Robinson > <SNIPPED OUT LOTS OF MONEY DATA TYPE STUFF> apparently, the money time is deprecated and will be going the way of the dinosaurs someday soon (so threaten the developers) we used the money data type extensively in an installation i run... with the news of money going out of style however =) we switched to numeric(9,2) which works quite well... still some quirks with numeric: no money->numeric (surprise), int2 doesn't play well with numeric (but converts easily to int4 which does)... it took me 2 days to rid our system of money and update all our code... and now it works much nicer, too! -- Aaron J. Seigo Sys Admin
Michael Robinson <robinson@netrinsics.com> writes:
> Is this fixed in later versions? If not, should I send in a patch?
What would you consider a patch? cash_div_flt8 rounds its result,
cash_div_int4 truncates. Which is right, and how many existing
apps might you break by changing the other one? How many of the
other money operators need to be tweaked too?
As Aaron points out, the money data type is looking awfully
dinosaur-like; nothing based on an int4 underlying representation
can possibly be really satisfactory for this purpose. The general
consensus on the hackers list has been that the money type should
be deprecated and eventually phased out. In the meantime, subtle
alterations of its behavior are of dubious value.
It seems to me, though, that the money type does offer a couple of
useful things that you don't get in raw NUMERIC; specifically,
input and output functions that are customized for currency display.
What really would be a useful project would be to reimplement money
as a thin overlay on NUMERIC, basically just input/output functions.
The interesting part of the job would be to do better in non-US
locales than we currently do; I don't think the money code is very
flexible about commas versus decimal points, for example.
regards, tom lane
"Aaron J. Seigo" <aaron@gtv.ca> writes: >still some quirks with numeric: no money->numeric (surprise), int2 doesn't play >well with numeric (but converts easily to int4 which does)... Do you pay taxes? ================ template1=> select 9.99::numeric(9,2) * 0.1; ERROR: Unable to identify an operator '*' for types 'numeric' and 'float8' You will have to retype this query usingan explicit cast template1=> select 9.99::numeric(9,2) * 0.1::float4; ERROR: Unable to identify an operator '*' for types 'numeric' and 'float4' You will have to retype this query usingan explicit cast ================ I need a type that exhibits correct financial rounding behavior in tax computations and currency conversions. My understanding is that in the U.S., you are supposed to compute to the mil, and then round. In China (my jurisdiction of concern), you just round to the nearest fen. -Michael Robinson
On 1999-12-05, Michael Robinson mentioned: > template1=> select 9.99::money / 10.0; > ?column? > -------- > $1.00 > (1 row) You should be using the numeric type. Money is deprecated. What you pointed out is probably only one of its problems. However, the numeric type seems to have some ideas of its own as well: => select 9.99::numeric(9,2) / 10.0::numeric(9,2); ?column? ------------ 0.9990000000 (1 row) What are the rules governing this situation? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> Is this fixed in later versions? If not, should I send in a patch?
Send patches. But there is a chance that the money type will be ripped
out for v7.0 (since afaik the numeric/decimal types supercede the
older hacked type).
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California