Thread: Python interface and Money?
I have a table that has a column of type money. A query such as "select sum(document_amount) from foo" returns the correct result when executed from psql, yet the same query returns the wrong answer (it appears to be a rounding issue--something like "1234.9999999999...") when executed through PygreSQL. Is there a workaround or a fix? While browsing the mailing list for an answer to this problem I found several references to the money type being deprecated. Is that the problem? This is PostgreSQL 7.1.3 on FreeBSD 4.2 TIA -- Joel
Thus spake Joel Mc Graw
> I have a table that has a column of type money. A query such as "select
> sum(document_amount) from foo" returns the correct result when executed
> from psql, yet the same query returns the wrong answer (it appears to be
> a rounding issue--something like "1234.9999999999...") when executed
> through PygreSQL. Is there a workaround or a fix?
That sounds like a Python issue. The correct value is passed to the
Python function as a string since it is always stored and delivered
correctly in the database. That's sort of the point of the money type.
I have been thinking of creating a money type in Python so that this
won't happen but haven't got around to it yet. In the meantime you
can always format it with "%.2f" to get the correct value displayed.
> While browsing the mailing list for an answer to this problem I found
> several references to the money type being deprecated. Is that the
> problem?
They keep saying that DECIMAL should be used instead but they are not
exactly equivalent. The MONEY type was originally written with locale
support and displays the local currency symbol.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
...
> > While browsing the mailing list for an answer to this problem I found
> > several references to the money type being deprecated. Is that the
> > problem?
> They keep saying that DECIMAL should be used instead but they are not
> exactly equivalent. The MONEY type was originally written with locale
> support and displays the local currency symbol.
Hmm. If we keep the money type then we should change the internal
implementation to use numeric or decimal or at least int64 instead of
int32. Comments?
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> Hmm. If we keep the money type then we should change the internal
> implementation to use numeric or decimal or at least int64 instead of
> int32. Comments?
I'd suggested some time ago that money should be rewritten as a thin
wrapper around numeric: it wouldn't have to do much except substitute
new I/O functions, and perhaps find a way to set the default scale
to match whatever is the customary number of monetary decimal places
in the current locale. I think this is already on the TODO list.
regards, tom lane
> I'd suggested some time ago that money should be rewritten as a thin > wrapper around numeric: it wouldn't have to do much except substitute > new I/O functions, and perhaps find a way to set the default scale > to match whatever is the customary number of monetary decimal places > in the current locale. I think this is already on the TODO list. It is: * Remove Money type, add money formatting for decimal type -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Thomas Lockhart
> > > While browsing the mailing list for an answer to this problem I found
> > > several references to the money type being deprecated. Is that the
> > > problem?
> > They keep saying that DECIMAL should be used instead but they are not
> > exactly equivalent. The MONEY type was originally written with locale
> > support and displays the local currency symbol.
>
> Hmm. If we keep the money type then we should change the internal
> implementation to use numeric or decimal or at least int64 instead of
> int32. Comments?
Definitely int64. Originally I wrote it as long for portability but I
think that the time has come to assume that everything that PostgreSQL
runs on can support 64 bit.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Thus spake Hannu Krosing > > I have been thinking of creating a money type in Python so that this > > won't happen but haven't got around to it yet. In the meantime you > > can always format it with "%.2f" to get the correct value displayed. > > There are pure python BigDecimal and Money types available in the same > place as m2crypto: > > http://www.post1.com/home/ngps/m2/ Cool. Two problems, however. I don't know if I can access this from my C code. This could simply be a learning issue. The other problem is in making sure that it is always available. It might make more sense to just create an internal type in pgmodule.c. > > They keep saying that DECIMAL should be used instead but they are not > > exactly equivalent. The MONEY type was originally written with locale > > support and displays the local currency symbol. > > This of course will be a real PITA to parse reliably in the adapter ;( Very true. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
"D'Arcy J.M. Cain" wrote: > > Thus spake Joel Mc Graw > > I have a table that has a column of type money. A query such as "select > > sum(document_amount) from foo" returns the correct result when executed > > from psql, yet the same query returns the wrong answer (it appears to be > > a rounding issue--something like "1234.9999999999...") when executed > > through PygreSQL. Is there a workaround or a fix? > > That sounds like a Python issue. The correct value is passed to the > Python function as a string since it is always stored and delivered > correctly in the database. That's sort of the point of the money type. > > I have been thinking of creating a money type in Python so that this > won't happen but haven't got around to it yet. In the meantime you > can always format it with "%.2f" to get the correct value displayed. There are pure python BigDecimal and Money types available in the same place as m2crypto: http://www.post1.com/home/ngps/m2/ Last time I checked the license seemed to be compatible. > > While browsing the mailing list for an answer to this problem I found > > several references to the money type being deprecated. Is that the > > problem? > > They keep saying that DECIMAL should be used instead but they are not > exactly equivalent. The MONEY type was originally written with locale > support and displays the local currency symbol. This of course will be a real PITA to parse reliably in the adapter ;( -------------- Hannu
Thomas Lockhart writes: > ... > > > While browsing the mailing list for an answer to this problem I found > > > several references to the money type being deprecated. Is that the > > > problem? > > They keep saying that DECIMAL should be used instead but they are not > > exactly equivalent. The MONEY type was originally written with locale > > support and displays the local currency symbol. > > Hmm. If we keep the money type then we should change the internal > implementation to use numeric or decimal or at least int64 instead of > int32. Comments? The purpose of a data type is to store data and attach a semantic value to it, not to be a tool for presentation. The money type doesn't even do the former correctly because when you change the locale, the amount stored changes. Since we have a very fine to_char() function, there is no longer a need for a separate money type, unless you want to have a type that stores a number plus a currency unit. Another reason why I personally consider the money type not very useful is that the very display format it touts makes it a PITA to parse the output in client applications. If you want to have a really silly analogy for why the money type is wrong, imagine a "length" type, that stores a number, and depending on the current locale, it prints meters or yards. (Note, it doesn't do the conversion, it just prints '#### meters' or '#### yards', where #### = ####.) I presume that most people would consider a length type nonsense in the first place, but that added feature makes it useless. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Monday 22 October 2001 13:45, Joel Mc Graw wrote:
> I have a table that has a column of type money. A query such as "select
> sum(document_amount) from foo" returns the correct result when executed
> from psql, yet the same query returns the wrong answer (it appears to be
> a rounding issue--something like "1234.9999999999...") when executed
> through PygreSQL. Is there a workaround or a fix?
The problem is that Python doesn't (yet) have a native decimal type and so the
closest thing we can convert to is float. There have been attempts at a
decimal type and at some point I will convert to one of them, hopefully the
same one that Python chooses as a standard.
> While browsing the mailing list for an answer to this problem I found
> several references to the money type being deprecated. Is that the
> problem?
There are people who want to remove it but I still think it serves a useful
purpose. I think that it needs to move to a 64 bit int and handle a lot more
type conversions but I moved some stuff to numeric due to the limits and
found a noticeable slowdown when working with lots of aggregates on it. The
int type is just more efficient. I plan on making a contrib version of the
type before it gets deleted if that is what is decided.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Wednesday 26 February 2003 08:53, D'Arcy J.M. Cain wrote:
> On Monday 22 October 2001 13:45, Joel Mc Graw wrote:
Doh! I was sitting in a saved mail folder and thought that I was responding
to a new message. Sorry about that.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.