Search code examples
postgresqlcolumn-types

Postgres column type for storing numbers with many decimals

I need to store in a column values like 0.0000003215218845

What type does my column needs to be? I've tried making it real or float but when I run a select query, to value I get is 3.21522e-07

What type does my column needs to be in order for the select query to return fully 0.0000003215218845 value?

Solution

  • If you want to control how a floating point number is converted to a string, use the to_char function:

    SELECT 0.0000003215218845::double precision;

     float8
-----------------
 3.215218845e-07
(1 row)

SELECT to_char(0.0000003215218845::double precision,
               'FM90.9999999999999999');

     to_char
------------------
 0.00000032152188
(1 row)

    The difference in value is the rounding error.

    Use numeric for values with arbitrary precision.