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 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, 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?
There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without the filter in the WHERE clause including cept.value::numeric) did not contain any infinity it should also work with the filter in the WHERE clause. Apparently a subplan executes this conversion in the WHERE before the other filters. I did not do any analyze to prove this.


Have you tried:

NULLIF(cept.value, 'inf')::numeric

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