Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity - Mailing list pgsql-general

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.

> 
> -- still has problem testing the range
> 
> 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  numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') 
> @> cept.value::numeric ORDER BY 1;
> ERROR:  cannot convert infinity to numeric

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 numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') 
@> cept.value::numeric
ORDER BY
     1;

So the above fails. In your title when you say there is no infinity that 
means the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields do not 
have any '-infinity' or 'infinity' values, correct?

> 
> 
> -- no problem if the query goes into its barrier and the test done outside
> with bar as (select it.id ,cept.value::numeric as val, 
> numrange(ceptl.min_alarm::numeric,ceptl.max_a
> larm::numeric,'()') as range from items it, cept_report cept , 
> dynacom.vessels vsl, machdefs md, cept_reportli
> mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND 
> it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs
> lstatus='Active' and md.application = 'Critical Equipment Performance 
> Test' AND cept.systemdate>= (now()-'1 ye
> ar'::interval)  ORDER BY 1)
> select * from bar where NOT range @> val;
> -- good data here

WITH bar AS (
     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_reportli mits ceptl
     WHERE
         it.id = cept.id
         AND md.defid = ceptl.defid
         AND it.defid = md.defid
         AND it.vslwhid = vsl.id
         AND vsl.vs lstatus = 'Active'
         AND md.application = 'Critical Equipment Performance Test'
         AND cept.systemdate >= (now() - '1 ye
ar'::interval)
     ORDER BY
         1
)
SELECT
     *
FROM
     bar
WHERE
     NOT RANGE @> val;

This version succeeds, correct? So breaking the range construction into 
one step and the @> test into another works. Though I am not sure why 
<range> @> changed to NOT <range> @>?
> 
> -- 
> Achilleas Mantzios
> DBA, Analyst, IT Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com