Re: Money casting too liberal? - Mailing list pgsql-general

On 2013-03-29, Gavan Schneider <pg-gts@snkmail.com> wrote:
> Some thoughts.
>
> The current MONEY type might be considered akin to ASCII.
> Perfect for a base US centric accounting system where there are
> cents and dollars and no need to carry smaller fractions. As
> discussed, there are some details that could be refined.
>
> When it comes to this type being used in full blown money
> systems it lacks the ability to carry fractions of cents and
> keep track of currencies. It also needs to play nicer with other
> exact types such as numeric, i.e., no intermediate calculations
> as real.
>
> Therefore the discussion is really about the desired role for
> the MONEY type. Should it be refined in its current dallar and
> cents mode? or, be promoted to a more universal role (akin to a
> shift from  ASCII to UTF)?
>
> If there is merit in making MONEY work for most situations
> involving financial transactions I think the following might apply:
>
> - keep integer as the underlying base type (for performance)

> - generalise the decimal multiplier of a MONRY column so a
> specific MONEY column can be what its creator wants (from
> partial cents to millions of dollars/Yen/Other, along with
> rounding/truncating rules as required by r the user of his/her
> external agencies)

I think a more generic denominator should to be allowed,
don't they trade in 64ths of a dollar on the stock market...

> - define the currency for a given column and only allow this to
> change in defined ways, and specifically forbid implicit changes
> such as would arise from altering LOCALE information

we've got that for collation on text columns, so that makes sense

> - ensure the MONEY type plays nice with other exact precision
> types, i.e., convert to REAL/FLOAT as a very last resort

> Personally I don't think it is appropriate for the MONEY type to
> have variable characteristics (such as different currencies)
> within a given column, rather the column variable should define
> the currency along with the desired decimal-multiplier and
> whatever else is required.

yeah, I can't see any case where this would be useful, doing that
would likely give the accountants nightmartes.  None of the
agregate functions that normally can be applied to number columns
coud be sensibly applied to such a column.

> The actual values within the column
> remain as simple integers. This is mostly based on performance
> issues. If the MONRY type is to be used it has to offer real
> performance benefits over bespoke NUMERIC applications.

I'm currently using it to allow easy localization.  performance isn't
really an issue.


maybe we should proposes a whole new type

fixed_point(n) which is integer column with a denominator "n" defined
on a per-column basis.




--
⚂⚃ 100% natural