How about "cannot cast jsonb $json_type to $sql_type" where $json_type
is the type inside the jsonb (e.g. string, number, boolean, array,
object)?
Yes, that sounds pretty good.
Does anybody have an objections to patch?
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9d2b89f90c..9f72984fac 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1857,7 +1857,7 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
/*
* Extract scalar value from raw-scalar pseudo-array jsonb.
*/
-static JsonbValue *
+static bool
JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res)
{
JsonbIterator *it;
@@ -1865,7 +1865,11 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res)
JsonbValue tmp;
if (!JsonContainerIsArray(jbc) || !JsonContainerIsScalar(jbc))
- return NULL;
+ {
+ /* inform caller about actual type of container */
+ res->type = (JsonContainerIsArray(jbc)) ? jbvArray : jbvObject;
+ return false;
+ }
/*
* A root scalar is stored as an array of one element, so we get the array
@@ -1887,7 +1891,40 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res)
tok = JsonbIteratorNext(&it, &tmp, true);
Assert(tok == WJB_DONE);
- return res;
+ return true;
+}
+
+/*
+ * Map jsonb types to human-readable names
+ */
+static char*
+JsonbTypeName(enum jbvType type)
+{
+ static struct
+ {
+ enum jbvType type;
+ char *typename;
+ }
+ names[] =
+ {
+ { jbvNull, "null" },
+ { jbvString, "string" },
+ { jbvNumeric, "numeric" },
+ { jbvBool, "boolean" },
+ { jbvArray, "array" },
+ { jbvObject, "object" },
+ { jbvBinary, "array or object" }
+ };
+ int i;
+
+ for(i=0; i<lengthof(names); i++)
+ if (names[i].type == type)
+ return names[i].typename;
+
+ /* should be unreachable */
+ elog(ERROR, "unknown jsonb type: %d", (int)type);
+
+ return NULL;
}
Datum
@@ -1899,7 +1936,7 @@ jsonb_bool(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvBool)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be boolean")));
+ errmsg("cannot cast jsonb %s to boolean", JsonbTypeName(v.type))));
PG_FREE_IF_COPY(in, 0);
@@ -1916,7 +1953,7 @@ jsonb_numeric(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to numeric", JsonbTypeName(v.type))));
/*
* v.val.numeric points into jsonb body, so we need to make a copy to
@@ -1939,7 +1976,7 @@ jsonb_int2(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to int2", JsonbTypeName(v.type))));
retValue = DirectFunctionCall1(numeric_int2,
NumericGetDatum(v.val.numeric));
@@ -1959,7 +1996,7 @@ jsonb_int4(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to int4", JsonbTypeName(v.type))));
retValue = DirectFunctionCall1(numeric_int4,
NumericGetDatum(v.val.numeric));
@@ -1979,7 +2016,7 @@ jsonb_int8(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to int8", JsonbTypeName(v.type))));
retValue = DirectFunctionCall1(numeric_int8,
NumericGetDatum(v.val.numeric));
@@ -1999,7 +2036,7 @@ jsonb_float4(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to float4", JsonbTypeName(v.type))));
retValue = DirectFunctionCall1(numeric_float4,
NumericGetDatum(v.val.numeric));
@@ -2019,7 +2056,7 @@ jsonb_float8(PG_FUNCTION_ARGS)
if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("jsonb value must be numeric")));
+ errmsg("cannot cast jsonb %s to float8", JsonbTypeName(v.type))));
retValue = DirectFunctionCall1(numeric_float8,
NumericGetDatum(v.val.numeric));
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index f705417b09..0be1fc97fc 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4321,7 +4321,7 @@ select 'true'::jsonb::bool;
(1 row)
select '[]'::jsonb::bool;
-ERROR: jsonb value must be boolean
+ERROR: cannot cast jsonb array to boolean
select '1.0'::jsonb::float;
float8
--------
@@ -4329,7 +4329,7 @@ select '1.0'::jsonb::float;
(1 row)
select '[1.0]'::jsonb::float;
-ERROR: jsonb value must be numeric
+ERROR: cannot cast jsonb array to float8
select '12345'::jsonb::int4;
int4
-------
@@ -4337,7 +4337,7 @@ select '12345'::jsonb::int4;
(1 row)
select '"hello"'::jsonb::int4;
-ERROR: jsonb value must be numeric
+ERROR: cannot cast jsonb string to int4
select '12345'::jsonb::numeric;
numeric
---------
@@ -4345,7 +4345,7 @@ select '12345'::jsonb::numeric;
(1 row)
select '{}'::jsonb::numeric;
-ERROR: jsonb value must be numeric
+ERROR: cannot cast jsonb object to numeric
select '12345.05'::jsonb::numeric;
numeric
----------
