Postgres column type for storing numbers with many decimals

Question

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?

Answer 1

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.

Answer 2

See postgres documentation: numeric-types v.9 or numeric v.11. could help. But you must be aware:

• The tool, you do your queries with, often does not return the data in full resolution. The numbers might be stored correctly, you must probably change the format, how you output it.
• float and double are stored as binary so the data might be rounded before being stored. So perhaps if you want to store decimal data in their exact form, the fixed-point types might be more suitable for your application.