postgres round function: unexpected behaviour in specific query

Question

This simple postgres round call rounds the numeric to 3 decimal places, as expected:

SELECT round(43.1235421,3);
 round  
--------
 43.124
(1 row)

However, the exact same round operation in the query below gives me 43.1240000000000023. The query is run against a postgis database, in case that makes a difference.

(SELECT ogc_fid,wkb_geometry,round(43.1235421,3) AS lat FROM grid_1) AS "grid"

This behaviour is undesired. What am I doing wrong? How can I get a float rounded to 3 decimal places, as expected? Thanks in advance.

EDIT: I tried explicitly casting the float as a numeric in the postgis query, but got the same result.

(SELECT ogc_fid,wkb_geometry,round(43.1235421::numeric,3) AS lat FROM grid_1) AS "grid"

Jasen · Accepted Answer · 2014-12-22 03:02:25Z

2

both give the same answer, the postgis one just gives more decimal places in the result, and some of them are worthless. floats are rarely precise, there are only a few fractions that have precise binary representations and 0.2 isn't one of them. if you extend the result to enough places you'll start to see the inprecision.

if you need base-10 precision use numeric instead of float.

if you still want floats, but want to hide the ugly part

 SET extra_float_digits TO 0;

edited Dec 22, 2014 at 3:02

answered Dec 22, 2014 at 2:54

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

2 Comments

kontextify Over a year ago

Casting to numeric gives me the same ugly "floats" for almost all input values, and turns out the mapping software (Tilemill) doesn't let me run a SET before the actual query. Is there any other way to just get the standard round() behaviour in a postgis db?

Jasen Over a year ago

I mean use numeric, not cast the result. although casting fo a fixed-width numeric would work too. select 43.1235421::numeric(6,3);

Collectives™ on Stack Overflow

postgres round function: unexpected behaviour in specific query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related