Re: Money casting too liberal?

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(at)snkmail(dot)com> 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 modelthe 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