> -----Original Message-----
> From: Mark [mailto:map@inter-resa.com]
> Sent: Thursday, July 03, 2003 5:52 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] float formating with xx.00
>
>
> On July 3, 2003 19:26 pm, Dann Corbit wrote:
> > connxdatasync=# create table test (foo float);
> > CREATE
> > connxdatasync=# insert into test values(1.0);
> > INSERT 29128 1
> > connxdatasync=# insert into test values(2.25);
> > INSERT 29129 1
> > connxdatasync=# insert into test values(1.27);
> > INSERT 29130 1
> > connxdatasync=# insert into test values(1.1);
> > INSERT 29131 1
> > connxdatasync=# select * from test;
> > foo
> > ------
> > 1
> > 2.25
> > 1.27
> > 1.1
> > (4 rows)
> >
> > connxdatasync=# select foo::numeric(12,2) from test;
> > ?column?
> > ----------
> > 1.00
> > 2.25
> > 1.27
> > 1.10
> > (4 rows)
> >
> > But don't do it. You'll be sorry. You really, really,
> really want to
> > store money as a numeric type. Otherwise, you might find some
> > surprises when columns almost never add up exactly.
> >
> > IMO-YMMV
>
>
> Thanks... but will a numeric data type for money display the
> same output as
> the input? Ex.: I enter, 19.1 will it show 19.10 or 19.1 ?
> Because your
> select example above is making a "199" value appear like
> "199.00" and many
> user wont appreciate this... for commercial reasons.
If you are saying that you want the program to 'remember' exactly what
you typed in and use that, then you are stuck with strings.
If I am storing money, I will use a decimal type. If the customer fails
to appreciate it, then they are dimwits. Dimwits make poor customers.
Storing money as floating point is a very bad idea. People who do that
are usually the rankest sort of amateurs.
Storing money as strings is a bad idea. People who do that are usually
rank amateurs.
Sensible ways to store money are:
1. BCD
2. Numeric
3. Decimal
4. Stored as a 64 bit integer (usually as 'pennies' but sometimes as
hundredths of a penny or thousandths of a penny)
5. Stored as an extended precision class of some sort or a custom
'money type'.
Are you aware that a long column of floating point numbers will give a
different answer when you sum it backwards as compared to forwards (and
no, I'm not kidding)?