Re: Formatting query output - Mailing list pgsql-general
From | Pedro Miguel Frazao Fernandes Ferreira |
---|---|
Subject | Re: Formatting query output |
Date | |
Msg-id | 3DBFAD7A.2000101@ualg.pt Whole thread Raw |
In response to | Formatting query output (Pedro Miguel Frazao Fernandes Ferreira <pfrazao@ualg.pt>) |
List | pgsql-general |
Tom Lane wrote: > Pedro Miguel Frazao Fernandes Ferreira <pfrazao@ualg.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
pgsql-general by date: