Thread: The Accountant is not Amused

The Accountant is not Amused

From
Michael Robinson
Date:
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=> 


Re: [HACKERS] The Accountant is not Amused

From
"Aaron J. Seigo"
Date:
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


Re: [HACKERS] The Accountant is not Amused

From
Tom Lane
Date:
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


Re: [HACKERS] The Accountant is not Amused

From
Michael Robinson
Date:
"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



Re: [HACKERS] The Accountant is not Amused

From
Peter Eisentraut
Date:
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




Re: [HACKERS] The Accountant is not Amused

From
Thomas Lockhart
Date:
> 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