Search code examples
postgresqldouble-precision

postgres query on double precision column not returning correct results

I have a double precision column in my database called "position."

If I have a row with a value in that column that is: 0.564593301435407

And I have a query that has a WHERE statement like the following:

WHERE "position" >= 0.564593301435407

(i.e., Querying for greater than or equal to that exact same value)

It does NOT return that column.

Why would this be?

Solution

  • I couldn't reproduce your exact problem: http://sqlfiddle.com/#!15/4cb15/6

    However, at 15 significant figures you're very close to the limit of accuracy on double precisions, and the fiddle suggests that the value might be getting rounded to 0.56459330143541 where a numeric would not be.

    I'd suggest that if high precision comparisons are a priority for you then numeric is a better choice of data type.