Money data type in PostgreSQL?

**Oliver Elphick** · Nov 12 '05, 12:58 AM

Re: Money data type in PostgreSQL?

On Wed, 2003-12-03 at 07:02, David Garamond wrote:[color=blue]
> 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?[/color]

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.
[color=blue]
> 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).[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**David Garamond** · Nov 12 '05, 12:58 AM

Re: Money data type in PostgreSQL?

Oliver Elphick wrote:[color=blue]
>
> 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.[/color]

I plan to store amount in a column (NUMERIC) and currency id in another
(CHAR(3)). Plus another column for amount in 'standard' currency (e.g.
USD; all addition/sum will be done to this column).
[color=blue]
> 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.)[/color]

--
dave

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Chris Travers** · Nov 12 '05, 12:58 AM

Re: Money data type in PostgreSQL?

Funny you mention IDR-- I happen to be in Jakarta at the moment.

Obviously different customers have different requirements, but I always
suggest trading performance for usefulness where necessary.

Obviously, the scale will need to be in accordance with the needs of your
customer (unlikey to need any for IDR; 2, 3, or more depending on industry
for USD). I would also always suggest overshooting the precision by a few
places to ensure that:
1) if there is another banking crisis, your app still performs
2) take into account future inflation
3) take into account future growth.

Best WIshes,
Chris Travers

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Claudio Lapidus** · Nov 12 '05, 12:58 AM

Re: Money data type in PostgreSQL?

David Garamond wrote:
[color=blue]
> I plan to store amount in a column (NUMERIC) and currency id in another
> (CHAR(3)). Plus another column for amount in 'standard' currency (e.g.
> USD; all addition/sum will be done to this column).[/color]

If your system really is going to handle multiple, simultaneous currencies,
beware of constant changes in the exchange rate between them. Probably
you'll be better by never storing anything in a 'standard currency' column
and doing instead all math on the fly, referring to a separate
'exchange_rates ' table when needed (i.e. always :-). Of course, all of this
has nothing to do with the technical solution but instead with the bussiness
rules the application must follow, so they must be incorporated early at the
spec level. In the end, probably an accountant will be the most qualified
one to define these things.

With regard to precision, it is common in certain applications the need to
handle very small amounts, especially when used as factors of a larger
calculation. I've even seen once some rates defined in hundredths of cents!

hth
cl.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Christopher Browne** · Nov 12 '05, 12:58 AM

Re: Money data type in PostgreSQL?

Martha Stewart called it a Good Thing when clapidus@hotmai l.com ("Claudio Lapidus") wrote:[color=blue]
> With regard to precision, it is common in certain applications the need to
> handle very small amounts, especially when used as factors of a larger
> calculation. I've even seen once some rates defined in hundredths of cents![/color]

Well, you don't need terribly much precision in the currency exchange
rate if the amount being converted is small. You only need a couple
significant digits to convert $0.24 USD to the appropriate amount in
$CDN.

But to get the pennies right on a $10,000 USD transaction converted
into GBP (UK Pounds), you need all the official precision that there
is. And if your calculation is off by 4 cents, some of those
accounting folk are liable to thrash you mercilessly over it. If you
get calculations WRONG, they get really uncomfortable, and want to
know why.
--
(reverse (concatenate 'string "ac.notelrac.te neerf" "@" "454aa"))

http://www3.sympatico.ca/cbbrowne/linuxdistributions.html

"Women who seek to be equal to men lack ambition. "
-- Timothy Leary

**Greg Stark** · Nov 12 '05, 12:59 AM

Re: Money data type in PostgreSQL?

"Claudio Lapidus" <clapidus@hotma il.com> writes:
[color=blue]
> With regard to precision, it is common in certain applications the need to
> handle very small amounts, especially when used as factors of a larger
> calculation. I've even seen once some rates defined in hundredths of cents![/color]

Normally there's nothing smaller than a tenth of a cent in US currency. It's
called a "mill". (or "mil"? I forget.). Of course you have to multiply
currency amounts by floating point numbers like interest rates or such, and
that will produce strange numbers but they'll always be rounded off to at
least mills and usually cents. You never actually debit or credit partial
mills.

At least that's how I was taught it was supposed to work.
I'm sure someone somewhere isn't following it.

--
greg

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Graham Leggett** · Nov 12 '05, 01:00 AM

Re: Money data type in PostgreSQL?

Christopher Browne wrote:
[color=blue]
> But to get the pennies right on a $10,000 USD transaction converted
> into GBP (UK Pounds), you need all the official precision that there
> is. And if your calculation is off by 4 cents, some of those
> accounting folk are liable to thrash you mercilessly over it. If you
> get calculations WRONG, they get really uncomfortable, and want to
> know why.[/color]

What I have done is store the currency amounts as bigints, at the same
precision defined for the currency (ie cents for dollars, pence for
pounds, etc). This guarantees that you don't get any rounding errors
when storing the figures as a floating point type. When manipulating the
numbers, I use Java BigDecimals, which don't lose any precision either,
and convert back to bigints to store in the database.

YMMV.

Regards,
Graham
--

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Jan Wieck** · Nov 12 '05, 01:00 AM

Re: Money data type in PostgreSQL?

Graham Leggett wrote:
[color=blue]
> Christopher Browne wrote:
>[color=green]
>> But to get the pennies right on a $10,000 USD transaction converted
>> into GBP (UK Pounds), you need all the official precision that there
>> is. And if your calculation is off by 4 cents, some of those
>> accounting folk are liable to thrash you mercilessly over it. If you
>> get calculations WRONG, they get really uncomfortable, and want to
>> know why.[/color]
>
> What I have done is store the currency amounts as bigints, at the same
> precision defined for the currency (ie cents for dollars, pence for
> pounds, etc). This guarantees that you don't get any rounding errors
> when storing the figures as a floating point type. When manipulating the
> numbers, I use Java BigDecimals, which don't lose any precision either,
> and convert back to bigints to store in the database.[/color]

You won't get any rounding errors in NUMERIC either. What people should
be concerned of is to find an arbitrary precision package for the
frontend programming language they're using.

Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== JanWieck@Yahoo. com #

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Craig O'Shannessy** · Nov 12 '05, 01:00 AM

Re: Money data type in PostgreSQL?

On Thu, 4 Dec 2003, Jan Wieck wrote:
[color=blue]
> Graham Leggett wrote:
>[color=green]
> > Christopher Browne wrote:
> >
> > What I have done is store the currency amounts as bigints, at the same
> > precision defined for the currency (ie cents for dollars, pence for
> > pounds, etc). This guarantees that you don't get any rounding errors
> > when storing the figures as a floating point type. When manipulating the
> > numbers, I use Java BigDecimals, which don't lose any precision either,
> > and convert back to bigints to store in the database.[/color]
>
> You won't get any rounding errors in NUMERIC either. What people should
> be concerned of is to find an arbitrary precision package for the
> frontend programming language they're using.
>[/color]

I agree, I use BigDecimal's in Java, and NUMERIC's in PostgreSQL, they
seem like a perfect match. Floating point numbers are not suitable for
money in my opinion.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Graham Leggett** · Nov 12 '05, 01:00 AM

Re: Money data type in PostgreSQL?

Jan Wieck wrote:
[color=blue]
> You won't get any rounding errors in NUMERIC either. What people should
> be concerned of is to find an arbitrary precision package for the
> frontend programming language they're using.[/color]

What is the definition of a numeric number? I understand (from studying
numeric methods all those years ago) that the base 10 decimal number 0.1
cannot be stored exactly in base 2 floating point, thus my use of
integers - is numeric an arbitrary precision concept?

Regards,
Graham
--

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Martijn van Oosterhout** · Nov 12 '05, 01:01 AM

Re: Money data type in PostgreSQL?

It's probably in the documentation, but numeric is stored as a string of
digits (or was it mibbles). In any case, if you wanted to list the weight of
the earth to 20 decimal places, numeric is for you. If you like you can
consider it integer arithmatic except the scaling is handled for you.

The format is NUMERIC(x,y) where x is the total number of digits and y is
the number of decimal places. There are rules about the results of
multiplication and division and such.

Hope this helps,

On Fri, Dec 05, 2003 at 02:39:42PM +0200, Graham Leggett wrote:[color=blue]
> Jan Wieck wrote:
> [color=green]
> >You won't get any rounding errors in NUMERIC either. What people should
> >be concerned of is to find an arbitrary precision package for the
> >frontend programming language they're using.[/color]
>
> What is the definition of a numeric number? I understand (from studying
> numeric methods all those years ago) that the base 10 decimal number 0.1
> cannot be stored exactly in base 2 floating point, thus my use of
> integers - is numeric an arbitrary precision concept?
>
> Regards,
> Graham
> --
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/0H6/Y5Twig3Ge+YRAqH qAJ9rB5VWdXEvTg +/e5/rfeMD5IOiowCfeK 5f
aBz+h6AkfQdep88 8hdxkuTw=
=dxy7
-----END PGP SIGNATURE-----

**Jan Wieck** · Nov 12 '05, 01:01 AM

Re: Money data type in PostgreSQL?

Graham Leggett wrote:[color=blue]
> Jan Wieck wrote:
>[color=green]
>> You won't get any rounding errors in NUMERIC either. What people should
>> be concerned of is to find an arbitrary precision package for the
>> frontend programming language they're using.[/color]
>
> What is the definition of a numeric number? I understand (from studying
> numeric methods all those years ago) that the base 10 decimal number 0.1
> cannot be stored exactly in base 2 floating point, thus my use of
> integers - is numeric an arbitrary precision concept?[/color]

The PostgreSQL datatype NUMERIC is performing decimal arithmetic. The
original I wrote used to do it string based, with one digit per byte but
stored the number as some sort of BCD, one digit per nibble. Tom Lane
changed that a while back into base 10,000 storage and calculation,
which has the advantages of doing 4 digits per loop and no need to
convert back and forth between the storage and the computational
representation.

Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== JanWieck@Yahoo. com #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)