From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Darafei "Komяpa" Praliaskouski <me@komzpa.net>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: Thu, 1 Mar 2018 13:20:29 +0300
Message-ID: <af9cb89f-390e-9dce-74cf-f50371116847@postgrespro.ru> (raw)
In-Reply-To: <CAC8Q8t+LDe9XMohGVnhNE3GgFhsVMJ38JvEVNf1DG1tg=mGD5Q@mail.gmail.com>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
<9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru>
<151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
<76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru>
<CAC8Q8t+LDe9XMohGVnhNE3GgFhsVMJ38JvEVNf1DG1tg=mGD5Q@mail.gmail.com>
On 01.03.2018 11:19, Darafei "Komяpa" Praliaskouski wrote:
> > Attached new version of the patch in which I removed duplicated code
> using new subroutine JsonbExtractScalar(). I am not sure what is
> better to do when a JSON item has an unexpected type: to throw an
> error or to return SQL NULL. Also JSON nulls could be converted to SQL
> NULLs.
>
> I would expect it to follow whatever is happening in JavaScript.
> I'm unsure about mapping of NULL and undefined/null though.
>
> > I should note here that expression (jb -> 'key')::datatype can be
> rewritten with SQL/JSON function JSON_VALUE: JSON_VALUE(jb, '$.key'
> RETURNING datatype ERROR ON ERROR)
>
> I would expect some casts to be implicit, so that chaining with other
> functions is possible:
>
> select ST_MakePoint(r->'lon', r->'lat');
>
> select sum(r->'income');
>
> > But by standard JSON_VALUE tries to cast string JSON items to the
> specified datatype too, so JSON_VALUE('{"key": "123"}'::jsonb, '$.key'
> RETURNING int ERROR ON ERROR) does not throw an error but returns 123.
>
> In actual JSON implementations number datatype is usually the one
> available in browsers, double precision.
> For some numbers (I've met this with nanoseconds) it leads to value
> being changed on subsequent serializations and deserializations, so
> it's common to wrap them in a string to be unchanged.
> So, I would expect that to work, but give me an exception if the
> datatype loses precision on conversion of specific value.
I think that only cast to a numeric type can be made implicit, because
it does not lose precision.
So, sum(jsonb) will work, but ST_MakePoint(float8, float8) still will
require an explicit cast.
It seems that in JavaScript we can implicitly cast strings to numerics
and unwrap one-element arrays.
Examples from Chrome:
> "123.45" / 3
41.15
> "1e100" / 3
3.333333333333333e+99
> "1e1000" / 3
Infinity
> "foo" / 3
NaN
> [123.45] / 3
41.15
> ["123.45"] / 3
41.15
> [123.45, 345] / 3
NaN
> undefined / 3
NaN
But null is converted to 0:
> null / 3
0
> null + 3
3
Below are examples showing how it works with new casts and JSON_VALUE:
=# SELECT '1234567890.1234567890'::jsonb::int2;
ERROR: cannot cast type jsonb to smallint
LINE 1: SELECT '1234567890.1234567890'::jsonb::int2;
^
=# SELECT '1234567890.1234567890'::jsonb::int4;
int4
------------
1234567890
(1 row)
=# SELECT '1234567890.1234567890'::jsonb::float4;
ERROR: cannot cast type jsonb to real
LINE 1: SELECT '1234567890.1234567890'::jsonb::float4;
^
=# SELECT '1234567890.1234567890'::jsonb::float8;
float8
------------------
1234567890.12346
(1 row)
=# SELECT '1234567890.1234567890'::jsonb::numeric;
numeric
-----------------------
1234567890.1234567890
(1 row)
=# SELECT '"1234567890.1234567890"'::jsonb::numeric;
ERROR: jsonb value must be numeric
=# SELECT 'null'::jsonb::numeric;
ERROR: jsonb value must be numeric
=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING int2 ERROR ON ERROR);
ERROR: smallint out of range
=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING int4 ERROR ON ERROR);
json_value
------------
1234567890
(1 row)
=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING float4 ERROR ON ERROR);
json_value
-------------
1.23457e+09
(1 row)
=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING float8 ERROR ON ERROR);
json_value
------------------
1234567890.12346
(1 row)
=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING numeric ERROR ON ERROR);
json_value
-----------------------
1234567890.1234567890
(1 row)
=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING int2 ERROR ON ERROR);
ERROR: value "1234567890.1234567890" is out of range for type smallint
=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING int4 ERROR ON ERROR);
ERROR: invalid input syntax for integer: "1234567890.1234567890"
=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING float4 ERROR ON ERROR);
json_value
-------------
1.23457e+09
(1 row)
=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING float8 ERROR ON ERROR);
json_value
------------------
1234567890.12346
(1 row)
=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING numeric ERROR ON ERROR);
json_value
-----------------------
1234567890.1234567890
(1 row)
=# SELECT JSON_VALUE('"foo"', '$' RETURNING numeric ERROR ON ERROR);
ERROR: invalid input syntax for type numeric: "foo"
=# SELECT JSON_VALUE('null', '$' RETURNING numeric ERROR ON ERROR);
json_value
------------
(1 row)
=# SELECT JSON_VALUE('{}', '$' RETURNING numeric ERROR ON ERROR);
ERROR: SQL/JSON scalar required
=# SELECT JSON_VALUE('[]', '$' RETURNING numeric ERROR ON ERROR);
ERROR: SQL/JSON scalar required
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, me@komzpa.net, a.lubennikova@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
In-Reply-To: <af9cb89f-390e-9dce-74cf-f50371116847@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox