CODEWITHSUNDEEP
postgresqlprecision
Francesco Meli
Francesco Meli

Reputation: 2700

When does PostgreSQL round a double precision column type

I have a colleague of mine that keeps telling me not to use a double precision type for a PostgreSQL column, because I will eventually have rounding issues.

I am only aware of one case where a value gets stored with approximation and is when a number with "too many" decimal digits gets saved.

For example if I try to store the result of 1/3, then I will get an approximation.

On the other hand, he is claiming that the above is not the only case. He is saying that sometimes, even if the user is trying to store a number with a well defined number of digits such as 84.2 or 3.124 the value might get save as 84.19 or 3.1239 for the second case

This sounds very strange to me.

Could anyone give me an example/proof that the above can actually happen?

Upvotes: 1

Views: 1771

Answers (1)

user330315
user330315

Reputation:

Your colleague is right: stay away from from float or double. But not so much because of rounding issue, but because those are approximate data types. What you put into that column is not necessarily what you get out.

If you care for precision and accurate values, use numeric.

A more detailed explanation about the pitfalls of approximate data types can be found here:

Upvotes: 4

Related Questions