I complained in the discussion of bug #18564 [1] that it's quite
inconsistent that you can cast a jsonb null to text and get
a SQL NULL:
=# select ('{"a": null}'::jsonb)->>'a';
?column?
----------
(1 row)
but if you cast it to any other type it's an error:
=# select (('{"a": null}'::jsonb)->'a')::float8;
ERROR: cannot cast jsonb null to type double precision
I think this should be allowed and should produce a SQL NULL.
It doesn't look hard: the attached POC patch fixes this for
the float8 case only. If there's not conceptual objections
I can flesh this out to cover the other jsonb-to-XXX
cast functions.
regards, tom lane
[1]
https://www.postgresql.org/message-id/flat/18564-5985f90678ed7512%40postgresql.org
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 928552d551..91f1059e4c 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2148,7 +2148,16 @@ jsonb_float8(PG_FUNCTION_ARGS)
JsonbValue v;
Datum retValue;
- if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+ if (!JsonbExtractScalar(&in->root, &v))
+ cannotCastJsonbValue(v.type, "double precision");
+
+ if (v.type == jbvNull)
+ {
+ PG_FREE_IF_COPY(in, 0);
+ PG_RETURN_NULL();
+ }
+
+ if (v.type != jbvNumeric)
cannotCastJsonbValue(v.type, "double precision");
retValue = DirectFunctionCall1(numeric_float8,
