PostgreSQL - get all integer digits from double precision field for use in to_char function

Question

As stated in the documentation of Postgres 9.0 the double precision data type has a precision of 15 decimal digits and a storage of 8 bytes, then an integer number larger than a normal bigint (8 bytes) stored in a double precision field is approximated. Correct me if I'm wrong, I say larger than a normal bigint because if you try to cast this number to bigint you get this error:

select 211116514527303268704::bigint;
>> ERROR:  bigint out of range

When you try to convert this to double precision and numeric and compare both you find that they're the same:

select 211116514527303268704::numeric,
211116514527303268704::double precision,
(211116514527303268704::double precision) = (211116514527303268704::numeric);
+-----------------------+----------------------+---------+
|        numeric        |        float8        | boolean |
+-----------------------+----------------------+---------+
| 211116514527303268704 | 2.11116514527303e+20 | t       |
+-----------------------+----------------------+---------+

and with the to_char() function they return different values:

select
trim(to_char((211116514527303268704::double precision),'999,999,999,999,999,999,999')),
trim(to_char((211116514527303268704::numeric),'999,999,999,999,999,999,999'));
+-----------------------------+-----------------------------+
|            text             |            text             |
+-----------------------------+-----------------------------+
| 211,116,514,527,303,270,400 | 211,116,514,527,303,268,704 |
+-----------------------------+-----------------------------+

As you can see the value returned with the to_char - numeric combination is correct but the to_char - double precision loses consistency from the exponential part in double precision 2.11116514527303e+20

I'm not sure if it affect something but the locale of lc_numeric is 'es_PY.utf8'

It's completely useless to implement double precision in this case or it's another alternative to keep those double precision fields? That is always the preferred option, there's some type of cast to numeric from double precision that keeps all the original digits?

For additional information I have a PostgreSQL 9.0 installation running in a CentOS 6 x86-64 server.

Erwin Brandstetter · Accepted Answer · 2015-05-28T17:26:07.000

1

The reason is that for the purpose of the equality comparison, the type with the higher resolution is cast to the type of the lower resolution. I.e.: in the example the numeric is cast to double precision.

Demo:

SELECT *
     , num = dp          AS no_cast
     , num::float8 = dp  AS dp_cast
     , num = dp::numeric AS num_cast
FROM  (
   SELECT numeric '211116514527303268705' AS num
        , float8  '211116514527303268705' AS dp
   ) t;

 num                  | dp                    | no_cast | dp_cast | num_cast
----------------------+-----------------------+---------+---------+---------
211116514527303268705 | 2.11116514527303e+020 | t       | t       | f

float8 is an alias of double precision.
Note that for other calculations, like addition, the type with the higher resolution is the result - which is a logic necessity. (Here the result is boolean anyway.)

edited May 28 '15 at 17:26

answered May 28 '15 at 17:10

Erwin Brandstetter

605,456
145
1,078
1,228

Yes, I noticed that the cast from float8 to numeric gives another result, then the part that exceeds the limit of 8 bytes is never recovered, the numeric data type is the only way to go if I want to keep all the values, correct me if I'm wrong, thanks for the very quick response! – Osuka May 28 '15 at 17:45
@Osuka: You are right. `numeric` is the only data type for this in standard Postgres. – Erwin Brandstetter May 28 '15 at 18:34
Thank you! I'll give you an upvote when I increase my reputation – Osuka May 28 '15 at 18:42

PostgreSQL - get all integer digits from double precision field for use in to_char function

1 Answers1