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

The cheat would be:

select version();
                                        version 

-------------------------------------------------------------------------------------
  PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
7.5.0, 64-bit

select nullif('inf'::float, 'inf')::numeric;
  nullif
--------
    NULL

select nullif(1.5::float, 'inf')::numeric;
  nullif
--------
     1.5
(1 row)

>>
>>>>> -- 
>>>>> Achilleas Mantzios
>>>>> DBA, Analyst, IT Lead
>>>>> IT DEPT
>>>>> Dynacom Tankers Mgmt
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com