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,

Reply via email to