Re: Formatting query output

Tom Lane wrote:
> Pedro Miguel Frazao Fernandes Ferreira <pfrazao(at)ualg(dot)pt> writes:
>
>>When I store a float8
>>number in a database, I am supposed to fetch the number as inserted.
>
>
> I fear you have a fundamental misconception about the nature of
> floating-point representation. There is no such thing as "fetching the
> number as inserted". You get only as much accuracy as float8 format
> will store, which is about half a digit less than you are asking for in
> this case.

Yes, I understand this. I must explain a bit more. All the float numbers
I will insert in PostgreSQL datababases will come from Matlab's double
type which is the same format as PostgreSQL float8 or a C double. When I
say "fetching the number as inserted" I am talking about numbers which
already come from an 8 byte float representation. If the number can be
stored in C or Matlab float 8 it can also be stored in PostgreSQL float8.

>
> The fact that you might be able to store this particular 16-digit value
> exactly is no guarantee that you'll be able to store other 16-digit
> values exactly, so I recommend choosing another representation if
> that's your requirement. Blaming the output formatting is the wrong
> way to look at it.

Its not a matter of blaming. I am not blaming anything neither anyone
work. I find PostgreSQL a very good example of open source high quality
software, which I use for long time for other type of applications, so
there's nothing to blame.
All I am saying is that float8 output in PostgreSQL query's is not
getting maximum precision. You can store a number with more precision
than the precision with which you can get it.
Do you understand that if I have a number stored in C double format and
I insert it in a database float8 field, I am supposed to be able to get
it back as it was stored in C double ?
Due to the way the output is formated currently this is not possible.
I now some people which needed to store double numbers which have
changed the code in src/backend/utils/adt/float.c because of this
problem. I just thought that instead of solving 'my' problem I should
report it so that it would be generally solved in order for PostgreSQL
to be used as storage for number crunching (in our case, distributed)
systems.

Again one example:
(4503599627370496 can be stored by a C double or PostgreSQL float8)

insert into test(real8) values (4503599627370496);
INSERT 21192 1

A select produces,

select real8 from test;
real8
---------------------
4.5035996273705e+15
(1 row)

In fact the number is correctly stored by PostgreSQL float8:

select to_char(real8,'9999999999999999999.99999') from test;
to_char
----------------------
4503599627370496
(1 row)

I hope you understand that all I want is that PostgreSQL can be used for
this type of application instead of other commercial database system's
currently used. A lot of people would start using it and maybe
supporting it.

Sorry if this message is a bit long.
Thanks for your reply and for PostgreSQL itself.

Best regards,
Pedro M. Ferreira

>
> regards, tom lane
>
>

--
----------------------------------------------------------------------
Pedro Miguel Frazao Fernandes Ferreira
Universidade do Algarve
Faculdade de Ciencias e Tecnologia
Campus de Gambelas
8000-117 Faro
Portugal
Tel./Fax: (+351) 289 800950 / 289 819403
http://w3.ualg.pt/~pfrazao