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;
ERROR:  invalid input syntax for type numeric: "inf"
LINE 1: select nullif(1.5, 'inf')::numeric;

Due to this:

https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-NULLIF

"The two arguments must be of comparable types. To be specific, they are 
compared exactly as if you had written value1 = value2, so there must be 
a suitable = operator available."

So:

1.5::numeric = 'inf'::numeric
> 
>>>> -- 
>>>> Achilleas Mantzios
>>>> DBA, Analyst, IT Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt



-- 
Adrian Klaver
adrian.klaver@aklaver.com