From a5579a90af05814eb5dc2fd5f68ce803899d2504 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 24 Jan 2025 13:20:44 -0500 Subject: Make jsonb casts to scalar types translate JSON null to SQL NULL. Formerly, these cases threw an error "cannot cast jsonb null to type ". That seems less than helpful though. It's also inconsistent with the behavior of the ->> operator, which translates JSON null to SQL NULL, as do some other jsonb functions. Discussion: https://postgr.es/m/3851203.1722552717@sss.pgh.pa.us --- src/backend/utils/adt/jsonb.c | 77 +++++++++++++++++++++++++++++++++++++++---- 1 file changed, 70 insertions(+), 7 deletions(-) (limited to 'src/backend/utils/adt/jsonb.c') diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index f4889d9ed72..8394a20e0e5 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -2040,7 +2040,16 @@ jsonb_bool(PG_FUNCTION_ARGS) Jsonb *in = PG_GETARG_JSONB_P(0); JsonbValue v; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvBool) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "boolean"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvBool) cannotCastJsonbValue(v.type, "boolean"); PG_FREE_IF_COPY(in, 0); @@ -2055,7 +2064,16 @@ jsonb_numeric(PG_FUNCTION_ARGS) JsonbValue v; Numeric retValue; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "numeric"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvNumeric) cannotCastJsonbValue(v.type, "numeric"); /* @@ -2076,7 +2094,16 @@ jsonb_int2(PG_FUNCTION_ARGS) JsonbValue v; Datum retValue; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "smallint"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvNumeric) cannotCastJsonbValue(v.type, "smallint"); retValue = DirectFunctionCall1(numeric_int2, @@ -2094,7 +2121,16 @@ jsonb_int4(PG_FUNCTION_ARGS) JsonbValue v; Datum retValue; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "integer"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvNumeric) cannotCastJsonbValue(v.type, "integer"); retValue = DirectFunctionCall1(numeric_int4, @@ -2112,7 +2148,16 @@ jsonb_int8(PG_FUNCTION_ARGS) JsonbValue v; Datum retValue; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "bigint"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvNumeric) cannotCastJsonbValue(v.type, "bigint"); retValue = DirectFunctionCall1(numeric_int8, @@ -2130,7 +2175,16 @@ jsonb_float4(PG_FUNCTION_ARGS) JsonbValue v; Datum retValue; - if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + if (!JsonbExtractScalar(&in->root, &v)) + cannotCastJsonbValue(v.type, "real"); + + if (v.type == jbvNull) + { + PG_FREE_IF_COPY(in, 0); + PG_RETURN_NULL(); + } + + if (v.type != jbvNumeric) cannotCastJsonbValue(v.type, "real"); retValue = DirectFunctionCall1(numeric_float4, @@ -2148,7 +2202,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, -- cgit v1.2.3