Hi,
this is the follow-up to a recent IRC discussion. The topic at hand is floating point values and I think there is one
problemwhich might be solvable only with a new feature. First, the problem:
I created a table
CREATE TABLE dummy ( v DOUBLE PRECISION );
so far, so good. Now, I would like to add values. Since floating point values tend to be problematic when using decimal
encoding,I opt for the binary encoding:
INSERT INTO dummy VALUES ( '0X1P-1022' );
this value itself is the problem. If I use pg_dump / pg_restore, the restore fails with:
COPY failed for table "dummy": ERROR: "2.22507385850720138e-308" is out of range for type double precision
This behavior might depend on the system's implementation of strtod(), I'm using Ubuntu 12.04.
Towards a solution:
While the problem occurs when importing the data back, the root (IMHO) is, that I can not request floating point values
(datatypesREAL and DOUBLE PRECISION) to be returned as strings with the hexadecimal notation (which would easily
preserveall bits). pg_dump should then also use this to retrieve the correct (bit-by-bit) value. Hence, I hope that you
couldcome up with a proper solution for this feature, and since you are far more experienced with PostgreSQL's
internalsand the possible ways to provide such a feature, I'll leave it to you to propose a syntax / flag / ...
Of course, if I missed something and retrieving the correct value is possible, please let me know. On IRC, we found
that"SET extra_float_digits=2" seems to work for the tests I have in my code, but I don't know if that is the correct
solutionfor all possible values of the floating point types. Using the hexadecimal notation feels like the natural
solutionto me, would give me much more confidence and, as a bonus, it would also improve efficiency, since it's much
easierthan decimal conversions.
Best regards, Daniel
