0

I'm trying to negate a double precision value inside jsonb data. I had to cast it to double since PG will return it as text. I found that zero value are printed as -0

Is that fine? how to fix it without "CASE WHEN"

enter image description here

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Mahmoud
  • 456
  • 3
  • 13

2 Answers2

2

If you add 0.0 to the result, it will normalize it to regular 0.

jjanes
  • 37,812
  • 5
  • 27
  • 34
0

As @Bergi asked: What is the problem? The properties of 0 and -0 are exactly the same. Run:

select '0 vs. -0. they are ' ||
       case when ( 0 = -0) 
            then 'Equal'
            else 'Not Equal'
       end;

Also see demo here. In all cases the results are the same. Multiplication produces -0 instead of 0, but since they are the same they can be freely substituted.

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Nice comparison table. Is this specified somewhere in the postgres documentation? I also tried out floating point operations that one would normally expect to distinguish them (`1/x`, `ln`, `log`, `cot`, `atan2`, `sign`), but they all have the same results with positive and negative zero. The only way of distinguishing them seems to be the `::text` cast. – Bergi Aug 10 '20 at 16:47
  • AFAIK nothing in the documentation, it's just the math. I had also looked at the logical functions (bitwise AND, OR, etc) but that requires casting to integer which turns -0 into 0 ( further conversion with ::text doesn't distinguishing them. Select (-0)::int4::text;) – Belayer Aug 10 '20 at 17:36