Обсуждение: float formating with xx.00
Hello, I am trying to enter 19.00 in a float field... no error but the data (select) is showing 19 without the "trailing" zeros... But for format and standard presentation of money, I need to keep any last zero digit. How can I do this? I run version 7.1x Thanks! -- Mark
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
> -----Original Message-----
> From: Mark [mailto:map@inter-resa.com]
> Sent: Thursday, July 03, 2003 1:29 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] float formating with xx.00
>
>
> Hello,
>
> I am trying to enter 19.00 in a float field... no error but
> the data (select)
> is showing 19 without the "trailing" zeros... But for
> format and standard
> presentation of money, I need to keep any last zero digit.
>
> How can I do this? I run version 7.1x
> Thanks!
> --
> Mark
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
> joining column's datatypes do not match
>
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 1:29 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] float formating with xx.00 > > > > > > Hello, > > > > I am trying to enter 19.00 in a float field... no error but > > the data (select) > > is showing 19 without the "trailing" zeros... But for > > format and standard > > presentation of money, I need to keep any last zero digit. > > > > How can I do this? I run version 7.1x > > Thanks! > > -- > > Mark > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index > > scan if your > > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match
> -----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