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

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(dot)klaver(at)aklaver(dot)com