title: PostgreSQL - get all integer digits from double precision field for use in to_char function
body: <p>As stated in the <a href="http://www.postgresql.org/docs/9.0/static/datatype-numeric.html" rel="nofollow">documentation of Postgres 9.0</a> the <code>double precision</code> data type has a precision of 15 decimal digits and a storage of 8 bytes, then an integer number larger than a normal <code>bigint</code> (8 bytes) stored in a <code>double precision</code> field is approximated. Correct me if I'm wrong, I say larger than a normal <code>bigint</code> because if you try to cast this number to <code>bigint</code> you get this error:</p>
  
  <pre><code>select 211116514527303268704::bigint;
  &gt;&gt; ERROR:  bigint out of range
  </code></pre>
  
  <p>When you try to convert this to <code>double precision</code> and <code>numeric</code> and compare both you find that they're the same:</p>
  
  <pre><code>select 211116514527303268704::numeric,
  211116514527303268704::double precision,
  (211116514527303268704::double precision) = (211116514527303268704::numeric);
  +-----------------------+----------------------+---------+
  |        numeric        |        float8        | boolean |
  +-----------------------+----------------------+---------+
  | 211116514527303268704 | 2.11116514527303e+20 | t       |
  +-----------------------+----------------------+---------+
  </code></pre>
  
  <p>and with the <code>to_char()</code> function they return different values:</p>
  
  <pre><code>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 |
  +-----------------------------+-----------------------------+
  </code></pre>
  
  <p>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 <code>double precision</code> 2.11116514527303e+20</p>
  
  <p>I'm not sure if it affect something but the locale of <code>lc_numeric</code> is 'es_PY.utf8'</p>
  
  <p>It's completely useless to implement <code>double precision</code> 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 <code>numeric</code> from <code>double precision</code> that keeps all the original digits?</p>
  
  <p>For additional information I have a PostgreSQL 9.0 installation running in a CentOS 6 x86-64 server.</p>