From: | Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | Peter Eisentraut <peter(at)eisentraut(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: More new SQL/JSON item methods |
Date: | 2023-12-07 13:24:31 |
Message-ID: | CAM2+6=UMCtXVSk1NmGHzsYYY6xGdnaMMKxVaKUtG7o0jkiuyQg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sun, Dec 3, 2023 at 9:44 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Hi Jeevan,
>
>
> I think these are in reasonably good shape, but there are a few things
> that concern me:
>
>
> andrew(at)~=# 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(at)~=# 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(at)~=# 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*
Attachment | Content-Type | Size |
---|---|---|
v4-0001-Add-numeric_int8_opt_error-to-optionally-suppress.patch | application/octet-stream | 3.0 KB |
v4-0002-Reorganise-jsonpath-Operators-and-Methods.patch | application/octet-stream | 15.2 KB |
v4-0005-Implement-jsonpath-.boolean-and-.string-methods.patch | application/octet-stream | 24.8 KB |
v4-0003-Implement-jsonpath-.number-.decimal-precision-sca.patch | application/octet-stream | 53.2 KB |
v4-0004-Implement-jsonpath-.date-.time-.time_tz-.timestam.patch | application/octet-stream | 93.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Verite | 2023-12-07 13:35:52 | Re: Emitting JSON to file using COPY TO |
Previous Message | Andrew Dunstan | 2023-12-07 13:19:19 | Re: Emitting JSON to file using COPY TO |