Re: Money data type in PostgreSQL?

On Wed, 2003-12-03 at 07:02, David Garamond wrote:
> What do people recommend for storing money amounts? I've seen people use
> NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate
> and why? This is considering various existing currencies, some having
> low rates (like IDR, in which you can have large amount up to hundreds
> of trillions) and some high rates (like USD, in which you can have small
> amount like 0.1 cent). Are there places/industries which involve values
> lower than 0.1 cent?

I think you should match the customer's data and use whatever precision
is necessary for it. The needs of a small shop will not be the same as a
currency trader's.

You should not regard amounts in different currencies as equivalent.
You cannot add Euros to dollars and get a meaningful figure; so they
should not be in the same column. If you are handling multiple
currencies, your database design needs to be a lot more sophisticated
than having a single money column.

> And what about 'factor' field in currency conversion table? Should I use
> REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC
> (exact numbers). The factor should range between 1E-3 (e.g. converting
> IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros).

You should only use NUMERIC for money; any kind of floating point
representation will lose detail somewhere along the line. (I suppose
you could use BIGINT for Japanese Yen.)

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"What shall we then say to these things? If God be for
us, who can be against us?" Romans 8:31