R: Rounding problems

["Paolo Saudin" <paolo@xxxxxxxxxxx>]

>Paolo Saudin wrote:

>Hi,

> 

>I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4.

>The first query with  -  cast( tables_seb.tbl_arvier_chamencon.id_1  AS numeric) AS value  - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS >value - give 1.3.

> 

>Which could be the reason ??

> 

> 

>My first thought is whats with all the castings???  
>
>Castings  are mostly likely the cause of your problems,   What is tbl_arvier_chamencon.id_1 data type???
>
>I'm guessing its something other than numeric. All other floating point data types will have problems caused by Binary Floating-Point Arithmetic
>
>Numeric data type uses different functions to do its math for the stated purpose of being exact yet being allot slower.
>
>In one query casting is done prior to avg() yet in the other casting is done after avg().   This will allow Postgres to use different functions to calculate average giving an unexpected >result.

 

 

Here is the table layout

 

CREATE TABLE tables_seb.tbl_arvier_chamencon

(

  fulldate timestamp without time zone NOT NULL DEFAULT '2000-01-01 00:00:00'::timestamp without time zone,

  id_1 real,

  id_1_cod smallint,

  id_2 real,

  id_2_cod smallint,

  id_3 real,

  id_3_cod smallint,

  id_4 real,

  id_4_cod smallint,

  CONSTRAINT tbl_arvier_chamencon_pkey PRIMARY KEY (fulldate)

) WITH (  OIDS=FALSE);

 

Thanks,

Paolo Saudin