From e3c78ab3e9f1f7cbfec9b04933fd774ae2008780 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 3 Aug 2024 14:43:46 -0400 Subject: [PATCH v1 1/2] 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 rather than 'null'. Discussion: https://postgr.es/m/3851203.1722552717@sss.pgh.pa.us --- src/backend/utils/adt/jsonb.c | 77 ++++++++++++++++++++++++++--- src/test/regress/expected/jsonb.out | 66 +++++++++++++++++++++++++ src/test/regress/sql/jsonb.sql | 13 +++++ 3 files changed, 149 insertions(+), 7 deletions(-) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 928552d551..ed054d5d42 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, diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index e66d760189..c0bd0e76ae 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5599,6 +5599,12 @@ select 'true'::jsonb::bool; t (1 row) +select 'null'::jsonb::bool; + bool +------ + +(1 row) + select '[]'::jsonb::bool; ERROR: cannot cast jsonb array to type boolean select '1.0'::jsonb::float; @@ -5607,22 +5613,82 @@ select '1.0'::jsonb::float; 1 (1 row) +select 'null'::jsonb::float; + float8 +-------- + +(1 row) + select '[1.0]'::jsonb::float; ERROR: cannot cast jsonb array to type double precision +select '1.0'::jsonb::float4; + float4 +-------- + 1 +(1 row) + +select 'null'::jsonb::float4; + float4 +-------- + +(1 row) + +select '[1.0]'::jsonb::float4; +ERROR: cannot cast jsonb array to type real +select '12345'::jsonb::int2; + int2 +------- + 12345 +(1 row) + +select 'null'::jsonb::int2; + int2 +------ + +(1 row) + +select '"hello"'::jsonb::int2; +ERROR: cannot cast jsonb string to type smallint select '12345'::jsonb::int4; int4 ------- 12345 (1 row) +select 'null'::jsonb::int4; + int4 +------ + +(1 row) + select '"hello"'::jsonb::int4; ERROR: cannot cast jsonb string to type integer +select '12345'::jsonb::int8; + int8 +------- + 12345 +(1 row) + +select 'null'::jsonb::int8; + int8 +------ + +(1 row) + +select '"hello"'::jsonb::int8; +ERROR: cannot cast jsonb string to type bigint select '12345'::jsonb::numeric; numeric --------- 12345 (1 row) +select 'null'::jsonb::numeric; + numeric +--------- + +(1 row) + select '{}'::jsonb::numeric; ERROR: cannot cast jsonb object to type numeric select '12345.05'::jsonb::numeric; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 97bc2242a1..1bcafe8cfb 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1537,12 +1537,25 @@ select ts_headline('[]'::jsonb, tsquery('aaa & bbb')); -- casts select 'true'::jsonb::bool; +select 'null'::jsonb::bool; select '[]'::jsonb::bool; select '1.0'::jsonb::float; +select 'null'::jsonb::float; select '[1.0]'::jsonb::float; +select '1.0'::jsonb::float4; +select 'null'::jsonb::float4; +select '[1.0]'::jsonb::float4; +select '12345'::jsonb::int2; +select 'null'::jsonb::int2; +select '"hello"'::jsonb::int2; select '12345'::jsonb::int4; +select 'null'::jsonb::int4; select '"hello"'::jsonb::int4; +select '12345'::jsonb::int8; +select 'null'::jsonb::int8; +select '"hello"'::jsonb::int8; select '12345'::jsonb::numeric; +select 'null'::jsonb::numeric; select '{}'::jsonb::numeric; select '12345.05'::jsonb::numeric; select '12345.05'::jsonb::float4; -- 2.43.5