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@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@gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::int4;
ERROR: 22P02: invalid input syntax for integer: "1234567890.1234567890"
LOCATION: pg_atoi, numutils.c:106
[local] gis@gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::float4;
┌─────────────┐
│ float4 │
├─────────────┤
│ 1.23457e+09 │
└─────────────┘
[local] gis@gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::float8;
┌──────────────────┐
│ float8 │
├──────────────────┤
│ 1234567890.12346 │
└──────────────────┘
[local] gis@gis=# SELECT ('1234567890.1234567890'::jsonb->>0)::numeric;
┌───────────────────────┐
│ numeric │
├───────────────────────┤
│ 1234567890.1234567890 │
└───────────────────────┘
[local] gis@gis=# SELECT ('null'::jsonb->>0)::numeric;
┌─────────┐
│ numeric │
├─────────┤
│ ¤ │
└─────────┘
[local] gis@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