Thread: round returns -0

I'm taking an standard deviation of a population and subtracting it from
the average of the same population and rounding the result. Sometimes
that result is negative and rounding it returns (or shows up as) a
negative zero (-0) in a SELECT.

basically:
SELECT
   client_name, avg(rpt_cnt),
   stddev_pop(rpt_cnt),
   round(avg(rpt_cnt) - stddev_pop(rpt_cnt))
from client_counts
group by client_name

and what I sometimes get is :
   client_name | a dp number | a dp number | -0

In postgresql-world, is -0 = 0?  Can I use that negative 0 in further
calculations without fear?  Is this a bug?

pg version is 9.2
OS is Windows 2003.

Thanks,

Wade Dare
"Committed to striving for an effort to try..."

Le 2013-03-06 à 21:42, Tony Dare a écrit :

> I'm taking an standard deviation of a population and subtracting it from the average of the same population and
roundingthe result. Sometimes that result is negative and rounding it returns (or shows up as) a negative zero (-0) in
aSELECT. 
>
> basically:
> SELECT
>  client_name, avg(rpt_cnt),
>  stddev_pop(rpt_cnt),
>  round(avg(rpt_cnt) - stddev_pop(rpt_cnt))
> from client_counts
> group by client_name
>
> and what I sometimes get is :
>  client_name | a dp number | a dp number | -0
>
> In postgresql-world, is -0 = 0?  Can I use that negative 0 in further calculations without fear?  Is this a bug?

This is related to the recent discussion of floating point values on this mailing list. You can read more about IEEE
754and whether 0 == -0 on Wikipedia: https://en.wikipedia.org/wiki/Signed_zero#Comparisons 

According to that article, IEEE 754 specifies that 0 == -0 in Java/C/etc.

Hope that helps!
François Beausoleil

On 03/06/2013 07:16 PM, François Beausoleil wrote:
> Le 2013-03-06 à 21:42, Tony Dare a écrit :
>
>> I'm taking an standard deviation of a population and subtracting it from the average of the same population and
roundingthe result. Sometimes that result is negative and rounding it returns (or shows up as) a negative zero (-0) in
aSELECT. 
>>
>> basically:
>> SELECT
>>   client_name, avg(rpt_cnt),
>>   stddev_pop(rpt_cnt),
>>   round(avg(rpt_cnt) - stddev_pop(rpt_cnt))
>> from client_counts
>> group by client_name
>>
>> and what I sometimes get is :
>>   client_name | a dp number | a dp number | -0
>>
>> In postgresql-world, is -0 = 0?  Can I use that negative 0 in further calculations without fear?  Is this a bug?
> This is related to the recent discussion of floating point values on this mailing list. You can read more about IEEE
754and whether 0 == -0 on Wikipedia: https://en.wikipedia.org/wiki/Signed_zero#Comparisons 
>
> According to that article, IEEE 754 specifies that 0 == -0 in Java/C/etc.
>
> Hope that helps!
> François Beausoleil
This is happening in a plpgsql function, so I guess that makes it C,
under the hood.  That does help, thank you.