Postgres Pro
Downloads
Home   >   mailing lists

Thread: HELP ! With Money type

HELP ! With Money type

From
Terry Hampton
Date:


    Everyone,

    Just ran across an odd situation.
    I have defined a field:

  amount       | money                    | not null default ''

    Works fine until the dollar amounts get
    very large  -  into the tens of millions.
    At that point, the actual amount stored
    becomes very unpredictable.

    I just saved a record via a simple
    front end App.  I entered 56,000,000
    When I retrieved the record i got:
        $13,050,327.04  !!

    Any thoughts ?

        ENVIRONMENT:
            Linux 2.4.20-20.7smp
            psql (PostgreSQL) 7.2.3

        Thank you,
        Terry


--
Terry L. Hampton
Project Manager
LimaCorp, LLC   www.limacorp.com
513.587.1874

Re: HELP ! With Money type

From
Oliver Elphick
Date:
On Fri, 2004-07-23 at 13:36, Terry Hampton wrote:
>
>     Everyone,
>
>     Just ran across an odd situation.
>     I have defined a field:
>
>   amount       | money                    | not null default ''
>
>     Works fine until the dollar amounts get
>     very large  -  into the tens of millions.
>     At that point, the actual amount stored
>     becomes very unpredictable.
>
>     I just saved a record via a simple
>     front end App.  I entered 56,000,000
>     When I retrieved the record i got:
>         $13,050,327.04  !!
>
>     Any thoughts ?

The Money type is deprecated.  It uses a floating point representation,
which is not good for precise values.

Use DECIMAL instead - e.g. DECIMAL(12,2)

Re: HELP ! With Money type

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> The Money type is deprecated.  It uses a floating point representation,
> which is not good for precise values.

Actually the underlying storage is integer (int4), which is precise but
has a very limited range.  Terry is seeing overflows (which are
evidently not being tested for :-().

            regards, tom lane