Skip site navigation (1) Skip section navigation (2)

Re: BUG #6217: to_char() gives incorrect output for very small float values

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chris Gernon" <kabigon(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6217: to_char() gives incorrect output for very small float values
Date: 2011-09-20 17:49:28
Message-ID: 11125.1316540968@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Chris Gernon" <kabigon(at)gmail(dot)com> writes:
> CREATE TABLE t (
> id serial,
> f double precision,
> CONSTRAINT t_pk PRIMARY KEY (id)
> );

> INSERT INTO t (f) VALUES (0.0000000000000000000000000000000563219288);

> ----------------------------------------

> SELECT to_char(f,
> 'FM999990.99999999999999999999999999999999999999999999999999') FROM t WHERE
> id = 1;

> Expected Output:
> 0.0000000000000000000000000000000563219288

> Actual Output:
> 0.

My immediate reaction to that is that float8 values don't have 57 digits
of precision.  If you are expecting that format string to do something
useful you should be applying it to a numeric column not a double
precision one.

It's possible that we can kluge things to make this particular case work
like you are expecting, but there are always going to be similar-looking
cases that can't work because the precision just isn't there.

(In a quick look at the code, the reason you just get "0." is that it's
rounding off after 15 digits to ensure it doesn't print garbage.  Maybe
it could be a bit smarter for cases where the value is very much smaller
than 1, but it wouldn't be a simple change.)

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Christopher GernonDate: 2011-09-20 17:49:50
Subject: Re: BUG #6217: to_char() gives incorrect output for very small float values
Previous:From: Kevin GrittnerDate: 2011-09-20 17:39:57
Subject: Re: BUG #6217: to_char() gives incorrect output for very small float values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group