Re: Money casting too liberal?

[Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>]

On 29/03/13 02:28, D'Arcy J.M. Cain wrote:

On Thu, 28 Mar 2013 23:43:23 +1100
Gavan Schneider <pg-gts@xxxxxxxxxxx> wrote:

But it appears that the philosophy does not extend to the "money"
type. ...

As the original author of the money type I guess I should weigh in.

select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11

It certainly doesn't accept that by design.  I just never thought about
such input. If you put garbage in anything can happen including
acceptance. If this is an issue I guess we need to look for such things
and reject it.  Just a SMOP.

I would defer to a CPA on the correct conventions for rounding. 
However I have a vague notion there are circumstances when 
rounding is always up, always down and (only sometimes) to the 
nearest. If the money type is meant to be serious then these 
conventions need to be followed/settable on a column by column 

Possible.  Generally I handle these issues in code because it is
sometimes hard to nail down exact requirements that fit all.  I also
tend to use money only in situations where the exact dollars and cents
is already known or is dealt with in code.

basis. And money is done in whole dollars, thousands of dollars, 
and fractional cents according to the situation, i.e., not just 
two decimal places... another setting.

I would like to see the type handle other situations such as foreign
(to me) currency, etc.  I suppose a positional parameter and a currency
string setting would handle most of those issues.  Technically, the
money type is a cents type.  Everything is stored as the number of
cents.  Formatting it as dollars and cents is a convenience added by
the I/O functions.

Personally I have ignored the money type in favour of numeric. 

Even as the author I sometimes go with numeric but there is a place for
the type.  If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost.  The big win that money brings is that
everything is stored as an int.  That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc.  The machine can generally work on the data as
it comes out of the DB.

I am (now) primarily a Java developer (in my bad past I have done FORTRAN, COBOL, & other languages ).

I use PostgreSQL in 2 situations:

1. To create a 'concrete sketch' of a sub set of a data model to explore how to represent things
   
   
2. To create an actual production database.
   

In either case, I want to represent data and constraints in ways that accurately model the data structures, and to provide 'implemented documentation'.  I think that the more semantics and constraints can be represented in the database the better - for 2 reasons: the declarative style is eaiser to follow than looking at program code, and it applies to _ALL_ accesses to the database (so it has more complete coverage and is hence more reliable)

So I would like a money type that I can use in all appropriate situations.


Cheers,
Gavin