Re: Cast jsonb to numeric, int, float, bool

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

What would be required to make ST_MakePoint(x, y) work?

Will ST_MakePoint(numeric, numeric) wrapper that performs the explicit be
enough?

Below are examples showing how it works with new casts and JSON_VALUE:
>
> =# SELECT '1234567890.1234567890'::jsonb::int2;

=# SELECT '1234567890.1234567890'::jsonb::int4;
> =# SELECT '1234567890.1234567890'::jsonb::float4;
> =# SELECT '1234567890.1234567890'::jsonb::float8;
> =# SELECT '1234567890.1234567890'::jsonb::numeric;

=# SELECT '"1234567890.1234567890"'::jsonb::numeric;

I would expect these to be equivalent to:

select ('"1234567890.1234567890"'::jsonb->>0)::numeric;

it probably makes sense in other cases:

[local] gis(at)gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::int2;
ERROR: 22003: value "1234567890.1234567890" is out of range for type
smallint
LOCATION: pg_atoi, numutils.c:83

-- another error message here ("cannot fit into type") will be fine here:
[local] gis(at)gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::int4;
ERROR: 22P02: invalid input syntax for integer: "1234567890.1234567890"
LOCATION: pg_atoi, numutils.c:106

[local] gis(at)gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::float4;
┌─────────────┐
│ float4 │
├─────────────┤
│ 1.23457e+09 │
└─────────────┘
[local] gis(at)gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::float8;
┌──────────────────┐
│ float8 │
├──────────────────┤
│ 1234567890.12346 │
└──────────────────┘
[local] gis(at)gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::numeric;
┌───────────────────────┐
│ numeric │
├───────────────────────┤
│ 1234567890.1234567890 │
└───────────────────────┘

[local] gis(at)gis=# SELECT ('null'::jsonb->>0)::numeric;
┌─────────┐
│ numeric │
├─────────┤
│ ¤ │
└─────────┘

[local] gis(at)gis=# SELECT ('"1234567890.1234567890"'::jsonb->>0)::numeric;
┌───────────────────────┐
│ numeric │
├───────────────────────┤
│ 1234567890.1234567890 │
└───────────────────────┘

Does this make sense, or are there hidden issues in this logic? :)

Darafei Praliaskouski,
GIS Engineer / Juno Minsk