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

On 19/7/22 20:32, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Thank you Adrian! Actually thank: https://sqlformat.darold.net/ Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε: On 7/19/22 03:38, Achilleas Mantzios wrote: I reformatted queries to see thing better.

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

On 19/7/22 20:31, David G. Johnston wrote: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: Thanks David Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR:  cannot convert infinity to numeric -- h

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

On 7/19/22 10:42 AM, Adrian Klaver wrote: On 7/19/22 10:32 AM, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Have you tried: NULLIF(cept.value, 'inf')::numeric That was a miss. I originally tested this on Postgres 14 and of course it worked. Trying it on Postgres 12

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

On 7/19/22 10:32 AM, Adrian Klaver wrote: On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Have you tried: NULLIF(cept.value, 'inf')::numeric That was a miss. I originally tested this on Postgres 14 and of course it worked. Trying it on Postgres 12 got: select nullif(1.5, 'inf')::numeric;

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

On 7/19/22 10:26 AM, Achilleas Mantzios wrote: Thank you Adrian! Actually thank: https://sqlformat.darold.net/ Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε: On 7/19/22 03:38, Achilleas Mantzios wrote: I reformatted queries to see thing better. AND numrange(ceptl.min_alarm::numeric, c

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

On Tuesday, July 19, 2022, Achilleas Mantzios wrote: > Thanks David > Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε: > > On Tuesday, July 19, 2022, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > >> >> ERROR: cannot convert infinity to numeric >> >> -- has no problem testing aga

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

Thank you Adrian! Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε: 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

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

Στις 19/7/22 17:23, ο/η Tom Lane έγραψε: "David G. Johnston" writes: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR: cannot convert infinity to numeric The column cept.value contains an infinity. I see nothing unusual in any of these queries given that fact. If you try to ca

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

Thanks David Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε: On Tuesday, July 19, 2022, Achilleas Mantzios wrote: ERROR:  cannot convert infinity to numeric -- has no problem testing against infinity select it.id ,cept.value::numeric as val, numrange(ceptl.

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

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

"David G. Johnston" writes: > On Tuesday, July 19, 2022, Achilleas Mantzios > wrote: >> ERROR: cannot convert infinity to numeric > 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

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

On Tuesday, July 19, 2022, Achilleas Mantzios 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_repo

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

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.v