Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Wed, 20 Jul 2022 15:27:05 +0300



    On 19/7/22 20:31, David G. Johnston
      wrote:

    
      On Tuesday, July 19, 2022, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>
      wrote:

      
          Thanks David

          
          Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

          
           On Tuesday, July 19, 2022, Achilleas
            Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>
            wrote:

            
                  ERROR:  cannot convert infinity to numeric

                  
                  -- has no problem testing against infinity

                  
                select
                    it.id
                    ,cept.value::numeric as val,
                    numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric

                  ,'()') as range from items it, cept_report cept ,
                  dynacom.vessels vsl, machdefs md, cept_reportlimits
                  ceptl wh

                  ere it.id=cept.id AND
                  md.defid=ceptl.defid AND it.defid=md.defid AND
                  it.vslwhid=vsl.id AND
                  vsl.vslstatus='Acti

                  ve' and md.application = 'Critical Equipment
                  Performance Test' AND cept.systemdate>= (now()-'1
                  year'::interval

                  ) AND  cept.value='inf' ORDER BY 1;             

                  id | val | range  

                  ----+-----+------- 

                  (0 rows)

                
            The column cept.value contains an infinity.  I see
              nothing unusual in any of these queries given that fact. 
              If you try to cast the infinity to numeric it will fail. 
              If that doesn’t happen the query won’t fail.
          
          Sorry I must have been dizzy today with so much support. 

          
          Yep, there are some infinity in there, but not in this
            result set.

          
          I think when the casting is in the WHERE filter for some
            reason some subplan uses this filter (and fails). But when
            this check is applied to the result, no infinity is found
            and works correctly.
        
      
      That is what it means for SQL to be a declarative language,
        the order of execution/evaluation is determined to be efficient
        and not what is explicitly written.  You do have some control
        though, but using it also means you might make things worse.
      

      I think you have issues anyway if you are doing equality
        checks on what seems to be a floating point column, regardless
        of which way you do the cast.
    
    Thank you, will look into it further when I get the time.

    
      David J.
      

    -- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt