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@tplus1.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
representmoney. 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.
Becausethe money datatype is locale-dependent, the type should effectively be treated as an opaque text value- the
valuein 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
moneygimmicks. 

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

Cheers,
M