Selecting floating point numbers in decimal form

Question

I've a small number in a PostgreSQL table:

test=# CREATE TABLE test (r real);
CREATE TABLE
test=# INSERT INTO test VALUES (0.00000000000000000000000000000000000000000009);
INSERT 0 1

When I run the following query it returns the number as 8.96831e-44:

test=# SELECT * FROM test;
      r      
-------------
 8.96831e-44
(1 row)

How can I show the value in psql in its decimal form (0.00000000000000000000000000000000000000000009) instead of the scientific notation? I'd be happy with 0.0000000000000000000000000000000000000000000896831 too. Unfortunately I can't change the table and I don't really care about loss of precision.

(I've played with to_char for a while with no success.)

Answer 1

Real in Postgres is a floating point datatype, stored on 4 bytes, that is 32 bits.

Your value,

0.00000000000000000000000000000000000000000009

Can not be precisely represented in a 32bit IEEE754 floating point number. You can check the exact values in this calculator

You cold try and use double precision (64bits) to store it, according to the calculator, that seems to be an exact representation. NOT TRUE Patricia showed that it was just the calculator rounding the value, even though explicitly asking it not to... Double would mean a bit more precision, but still no exact value, as this number is not representable using finite number of binary digits. (Thanks, Patricia, a lesson learnt (again): don't believe what you see on the Intertubez)

Under normal circumstances, you should use a NUMERIC(precision, scale) format, that would store the number precisely to get back the correct value.

However, your value to store seems to have a scale larger than postgres allows (which seems to be 30) for exact decimal represenations. If you don't want to do calculations, just store them (which would not be a very common situation, I admit), you could try storing them as strings... (but this is ugly...)

EDIT

This to_char problem seems to be a known bug...

Quote:

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.

(from here)

However, I find this not defendable. IMHO a double (IEEE754 64bit floating point to be exact) will always have ~15 significant decimal digits, if the value fits into the type...

Recommended reading:

• What Every Computer Scientist Should Know About Floating-Point Arithmetic
• Postgres numeric types
• BUG #6217: to_char() gives incorrect output for very small float values