This error occurs when using PostgreSQL SQL/JSON functions like JSON_VALUE() or JSON_QUERY() and attempting to cast a JSON value to an incompatible SQL type. It typically indicates a type mismatch between the JSON content and the target data type.
PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, JSON_EXISTS) provide powerful ways to query and extract data from JSON documents. When these functions evaluate a JSON path and attempt to coerce the result to a specific SQL type, PostgreSQL validates that the conversion is valid. This error is raised when the JSON value's type is incompatible with the requested target type—for example, trying to cast a JSON string to a numeric type, a JSON array to a scalar type, or a JSON null to a non-nullable type without proper error handling. The error can occur in two scenarios: during JSON path evaluation when the result doesn't match expectations, or during the type coercion phase when PostgreSQL cannot safely convert the JSON value to the target SQL type.
Add an ON ERROR clause to your JSON function to specify behavior when casting fails. By default, ON ERROR returns NULL:
SELECT JSON_VALUE(data, '$.price' RETURNING numeric DEFAULT NULL ON ERROR)
FROM products;Alternatively, use ERROR ON ERROR to see the actual error for debugging:
SELECT JSON_VALUE(data, '$.price' RETURNING numeric ERROR ON ERROR)
FROM products;When dealing with JSON string values that represent numbers, extract as text first, then cast to the target type:
-- Don't do this:
SELECT (data -> 'count')::numeric FROM items; -- Fails if count is a string
-- Do this instead:
SELECT (data ->> 'count')::text::numeric FROM items;
-- Or with JSON functions:
SELECT CAST(JSON_VALUE(data, '$.count' RETURNING text) AS numeric) FROM items;The ->> operator extracts as text, which is safer for subsequent type conversions.
Verify that your JSON path will extract the correct type of value. Use JSON_EXISTS to check if a path exists before trying to extract it:
-- Check if value exists and is the right type
SELECT
CASE
WHEN JSON_EXISTS(data, '$.price' ? @ > 0) THEN
JSON_VALUE(data, '$.price' RETURNING numeric)
ELSE
NULL
END AS price
FROM products;Make sure your RETURNING clause specifies a type that PostgreSQL can actually cast the JSON value to:
-- For JSON numeric values:
SELECT JSON_VALUE(data, '$.count' RETURNING bigint) FROM items;
-- For JSON string values:
SELECT JSON_VALUE(data, '$.name' RETURNING text) FROM items;
-- For JSON boolean values:
SELECT JSON_VALUE(data, '$.is_active' RETURNING boolean) FROM items;If the JSON value's actual type doesn't match, use ON ERROR to handle it gracefully.
JSON null values cannot be cast directly to most PostgreSQL types. Use COALESCE or DEFAULT to provide a fallback:
-- With ON ERROR (returns NULL by default):
SELECT COALESCE(
JSON_VALUE(data, '$.age' RETURNING integer),
0 -- Default value if JSON value is null or casting fails
) FROM users;
-- Or use DEFAULT in ON EMPTY clause:
SELECT JSON_VALUE(
data,
'$.age'
RETURNING integer
NULL ON EMPTY
NULL ON ERROR
) FROM users;PostgreSQL's SQL/JSON path language is powerful but strict about type safety. Understanding the difference between JSON types (string, number, boolean, array, object, null) and PostgreSQL types (text, numeric, boolean, etc.) is critical. When working with JSONB columns that might contain heterogeneous data, consider validating the JSON structure at insert time or using JSON schemas. For complex transformations, breaking the operation into multiple steps (extract as text, validate, then cast) is often clearer and more maintainable than trying to do everything in one expression. The -> operator returns jsonb while ->> returns text—using ->> first gives you more flexibility for subsequent operations.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL