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

On Tuesday, July 19, 2022, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
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.

David J.