Re: Money data type in PostgreSQL? - Mailing list pgsql-general

From Oliver Elphick
Subject Re: Money data type in PostgreSQL?
Date
Msg-id 1070438934.1460.187.camel@linda.lfix.co.uk
Whole thread Raw
In response to Money data type in PostgreSQL?  (David Garamond <lists@zara.6.isreserved.com>)
Responses Re: Money data type in PostgreSQL?  (David Garamond <lists@zara.6.isreserved.com>)
List pgsql-general
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.Elphick@lfix.co.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


pgsql-general by date:

Previous
From: "John Sidney-Woollett"
Date:
Subject: Transaction Question
Next
From: David Garamond
Date:
Subject: Re: Money data type in PostgreSQL?