float formating with xx.00

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

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.

Best regards,
--
Mark

-----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)?

/*
**
** Not a surprise, to them that knows:
**
*/
#include <stdio.h>
#include <stdlib.h>

#define A_LEN 500

static float foo[A_LEN];

static double bar[A_LEN];

int
main (void)
{
long i;
double d;
float f;
for (i = 0; i < A_LEN; i++)
{
d = rand () / (rand () + 1.0);
d *= d;
if (rand () % 2)
d = -d;
foo[i] = (float) d;
bar[i] = d;
}
f = 0;
d = 0;
for (i = 0; i < A_LEN; i++)
{
f += foo[i];
d += bar[i];
}
printf ("forward float sum = %.20f\n", f);
printf ("forward double sum = %.20f\n", d);
f = 0;
d = 0;
for (i = A_LEN - 1; i >= 0; i--)
{
f += foo[i];
d += bar[i];
}
printf ("backward float sum = %.20f\n", f);
printf ("backward double sum = %.20f\n", d);
return 0;
}
/*

OpenVMS VAX:

$ run foo
forward float sum = -6724682.50000000000000000000
forward double sum = -6724682.13690311496610000000
backward float sum = -6724679.50000000000000000000
backward double sum = -6724682.13690311496610000000

OpenVMS Alpha:
$ run foo
forward float sum = -6724682.50000000000000000000
forward double sum = -6724682.13690311090000000000
backward float sum = -6724679.50000000000000000000
backward double sum = -6724682.13690311370000000000
$

GCC on AMD Athlon:
$ ./a
forward float sum = -56051.43860578643943881616
forward double sum = -56051.43626179593411507085
backward float sum = -56051.43860578643943881616
backward double sum = -56051.43626179593411507085

MS VC++ 6 on AMD Athlon with /Ox flag:

C:\tmp>foo
forward float sum = 545785.98050410976000000000
forward double sum = 545786.01145155274000000000
backward float sum = 545785.98050410964000000000
backward double sum = 545786.01145155251000000000

MS VC++ 6 on AMD Athlon with /Op flag (forces memory storage):

C:\tmp>foo
forward float sum = 545785.93750000000000000000
forward double sum = 545786.01145155274000000000
backward float sum = 545785.56250000000000000000
backward double sum = 545786.01145155251000000000

*/

"Dann Corbit" <DCorbit@connx.com> writes:

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 ?

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.

No, actually a column declared "numeric" (without any specific
precision) will do that for him. This is a better choice than
using a string IMHO.

regression=# select '123.45'::numeric;
numeric
---------
123.45
(1 row)

regression=# select '123.4'::numeric;
numeric
---------
123.4
(1 row)

regression=# select '123.0'::numeric;
numeric
---------
123.0
(1 row)

Not SQL-spec AFAIR, but Postgres will take it.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 03, 2003 6:33 PM
To: Dann Corbit
Cc: map@inter-resa.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] float formating with xx.00

"Dann Corbit" <DCorbit@connx.com> writes:

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 ?

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.

No, actually a column declared "numeric" (without any specific
precision) will do that for him. This is a better choice
than using a string IMHO.

regression=# select '123.45'::numeric;
numeric
---------
123.45
(1 row)

regression=# select '123.4'::numeric;
numeric
---------
123.4
(1 row)

regression=# select '123.0'::numeric;
numeric
---------
123.0
(1 row)

Not SQL-spec AFAIR, but Postgres will take it.

Can't say I am really positive what he was after. But I am not sure
that numeric without precision or scale qualfication will achieve his
goals either.

connxdatasync=# insert into test values (.99999999999999999999999999999)
connxdatasync-# ;
INSERT 29198 1
connxdatasync=# insert into test
values(99999999999999.99999999999999999999999999999999999999999999999999
999999999999999)
connxdatasync-# ;
INSERT 29199 1
connxdatasync=# create table test2 (foo numeric);
CREATE
connxdatasync=# insert into test2 values
(0.00000000000000000000000000000000000000);
INSERT 29210 1
connxdatasync=# insert into test2 values
(10.000000000000000000000000000000000000001);
INSERT 29211 1
connxdatasync=# insert into test2 values
(10.00000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000001);
INSERT 29212 1
connxdatasync=# insert into test2 values
(0.000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000001);
INSERT 29213 1
connxdatasync=# insert into test values (1.0/7.0);
INSERT 29214 1
connxdatasync=# insert into test2 values (1.0/7.0);
INSERT 29215 1
connxdatasync=# select foo::numeric from test;
?column?
-------------------
1
2.25
1.27
1.1
1
100000000000000
0.142857142857143
(7 rows)

connxdatasync=# select foo from test2;
foo
-----------
0.000000
10.000000
10.000000
0.000000
0.142857
(5 rows)

Don't know if this is the sort of result hoped for or not. Probably
not.

Now, this is 7.1.3. Don't know if 7.4 (or other flavors) behave
differently.

On July 3, 2003 21:32 pm, Tom Lane wrote:

"Dann Corbit" <DCorbit@connx.com> writes:

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 ?

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.

No, actually a column declared "numeric" (without any specific
precision) will do that for him. This is a better choice than
using a string IMHO.

regression=# select '123.45'::numeric;
numeric
---------
123.45
(1 row)

regression=# select '123.4'::numeric;
numeric
---------
123.4
(1 row)

regression=# select '123.0'::numeric;
numeric
---------
123.0
(1 row)

Hello,

Thank you for this insight... but I tried to enter "20.00" into a:
foo numeric type
foo float4 type
or even a:
foo numeric(12,2) type

with the same result: a select foo::numeric from table; always gives me "20"
just like select foo from table;

In the case of numeric(12,2), I always get the trailing double digits... so
"20" entered gives "20.00" with the select foo (with or without the
::numeric).

??? Did miss something from your explanation or is it my 7.1.x version taht
is the problem?

Thanks,

"Dann Corbit" <DCorbit@connx.com> writes:

Now, this is 7.1.3. Don't know if 7.4 (or other flavors) behave
differently.

Quite differently ... in 7.2 and later:

regression=# create table test2(foo numeric);
CREATE
regression=# insert into test2 values (10.00000000000000000000001);
INSERT 148451 1
regression=# insert into test2 values (10.000000000000000000000000000000000000000000000000000000000000000000001);
INSERT 148452 1
regression=# select * from test2;
foo
--------------------------------------------------------------------------
10.00000000000000000000001
10.000000000000000000000000000000000000000000000000000000000000000000001
(2 rows)

regression=#

regards, tom lane