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?

you can store them in float ok. 3.215218845e-07 is equal to 0.0000003215218845 — Vao Tsun
– Vao Tsun, Commented Aug 1, 2017 at 12:52
Float lacks real precision. If you need an exact answer it should be avoided. Numeric has a limit that's in the thousands of characters and still be accurate, but it's slower. — Scott Marlowe
– Scott Marlowe, Commented Aug 1, 2017 at 20:55

Laurenz Albe · Accepted Answer · 2017-08-01 13:23:06Z

2

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.

answered Aug 1, 2017 at 13:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alin Over a year ago

Finally something that works. From what I've read in order to perform calculations exactly I would need either numeric or decimal. But how should I specify the precision and scale to fit all the values up to 20 decimals for instance. I need to do the calculations exactly so a type which isn't stored exactly won't work

Laurenz Albe Over a year ago

Use a type like numeric(21,20) (that would be for one position before the decimal separator and 20 after).

Scott Marlowe Over a year ago

Note that you do not HAVE to declare the precision.

aschoerk · Accepted Answer · 2020-04-14 21:08:07Z

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.

edited Apr 14, 2020 at 21:08

answered Aug 1, 2017 at 13:08

aschoerk

3,6632 gold badges17 silver badges32 bronze badges

Collectives™ on Stack Overflow

Postgres column type for storing numbers with many decimals

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related