Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux