Re: More new SQL/JSON item methods

On Sun, Dec 3, 2023 at 9:44 PM Andrew Dunstan <andrew@dunslane.net> wrote:

> Hi Jeevan,
>
>
> I think these are in reasonably good shape, but there are a few things
> that concern me:
>
>
> andrew@~=# select jsonb_path_query_array('[1.2]', '$[*].bigint()');
> ERROR:  numeric argument of jsonpath item method .bigint() is out of range
> for type bigint
>
> I'm ok with this being an error, but I think the error message is wrong.
> It should be the "invalid input" message.
>
> andrew@~=# select jsonb_path_query_array('[1.0]', '$[*].bigint()');
> ERROR:  numeric argument of jsonpath item method .bigint() is out of range
> for type bigint
>
> Should we trim trailing dot+zeros from numeric values before trying to
> convert to bigint/int? If not, this too should be an "invalid input" case.
>

We have the same issue with integer conversion and need a fix.

Unfortunately, I was using int8in() for the conversion of numeric values.
We should be using numeric_int8() instead. However, there is no opt_error
version of the same.

So, I have introduced a numeric_int8_opt_error() version just like we have
one for int4, i.e. numeric_int4_opt_error(), to suppress the error. These
changes are in the 0001 patch. (All other patch numbers are now increased
by 1)

I have used this new function to fix this reported issue and used
numeric_int4_opt_error() for integer conversion.


> andrew@~=# select jsonb_path_query_array('[1.0]', '$[*].boolean()');
> ERROR:  numeric argument of jsonpath item method .boolean() is out of
> range for type boolean
>
> It seems odd that any non-zero integer is true but not any non-zero
> numeric. Is that in the spec? If not I'd avoid trying to convert it to an
> integer first, and just check for infinity/nan before looking to see if
> it's zero.
>
PostgreSQL doesn’t cast a numeric to boolean. So maybe we should keep this
behavior as is.

# select 1.0::boolean;
ERROR:  cannot cast type numeric to boolean
LINE 1: select 1.0::boolean;


> The code for integer() and bigint() seems a bit duplicative, but I'm not
> sure there's a clean way of avoiding that.
>
> The items for datetime types and string look OK.
>
Thanks.

Suggestions?


>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>

-- 
Jeevan Chalke

*Senior Staff SDE, Database Architect, and ManagerProduct Development*



edbpostgres.com