Re: How to use the postgresql money type?

On Jan 19, 2011, at 6:42 PM, Daniele Varrazzo wrote:

> On Wed, Jan 19, 2011 at 8:06 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:
>> PostgreSQL has a money type, but I don't know how to use it with
>> psycopg2. Do I need to write my own code to convert to and from SQL?
>
>
> I also see that PostgreSQL doesn't let you convert from decimal to
> money: bad stuff

That's because non-integer numbers in postgresql are interpreted as floats, which is exactly what you do not want to represent money. It's the same in python:

>>> from decimal import Decimal
>>> Decimal(100.00)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py", line 652, in __new__
"First convert the float to a string")
TypeError: Cannot convert float to Decimal. First convert the float to a string

So the example which accepts floats and converts the input to a string includes a critical bug, not a workaround. Because the money datatype is locale-dependent, the type should effectively be treated as an opaque text value- the value in the database must be used as-is for display to the user (including the decimal delimiter and currency indicator). Math on money must be done in the database- this is required to properly handle banker's rounding and other money gimmicks.

For the original poster, if these restrictions are in your way, then look at NUMERIC(precision,scale) for an effective equivalent to python's Decimal class.

Cheers,
M