pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity - Mailing list pgsql-general
| From | Achilleas Mantzios |
|---|---|
| Subject | pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity |
| Date | |
| Msg-id | 1c9f27e7-459e-2e05-7129-e9e14ca503b7@matrix.gatewaynet.com Whole thread Raw |
| Responses |
Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
dynacom=# 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 < -100 ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)
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::numeric>'-1' ORDER BY 1;
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)
-- 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
-- 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
,'()') 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 < -100 ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)
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::numeric>'-1' ORDER BY 1;
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)
-- 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
-- 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
-- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt
pgsql-general by date: