Bug / feature request for floating point to string conversion

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 problem which 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 (datatypes REAL and DOUBLE PRECISION) to be returned as strings with the hexadecimal notation (which would easily preserve all bits). pg_dump should then also use this to retrieve the correct (bit-by-bit) value. Hence, I hope that you could come up with a proper solution for this feature, and since you are far more experienced with PostgreSQL's internals and 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 solution for all possible values of the floating point types. Using the hexadecimal notation feels like the natural solution to me, would give me much more confidence and, as a bonus, it would also improve efficiency, since it's much easier than decimal conversions.

Best regards, Daniel