From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity |
Date: | 2022-07-20 12:28:48 |
Message-ID: | 6302af7a-a8f2-4bc4-78ce-3770bf266f5b@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>>>
>>>>
>
>>> 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
no, cause the CTE version worked. Will keep in mind for similar future problems.
>
>>>> --
>>>> Achilleas Mantzios
>>>> DBA, Analyst, IT Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>>>
>>>
>>>
>>
>>
>
>
--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
From | Date | Subject | |
---|---|---|---|
Next Message | Karthik K L V | 2022-07-20 12:59:37 | Re: operator does not exist: text = bytea |
Previous Message | houzj.fnst@fujitsu.com | 2022-07-20 12:28:26 | RE: Support logical replication of DDLs |