This error occurs when a JSON_VALUE() or jsonb_path_query() call in strict mode tries to extract a numeric value from a JSON path that doesn't contain a number or doesn't exist. Use ON ERROR/ON EMPTY clauses or switch to lax mode to handle missing numeric values.
PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, jsonb_path_query) can operate in two modes: lax (default) and strict. In strict mode, the database enforces strict structural rules. When you use a JSON path expression that tries to access a numeric value but the path doesn't exist, the value is not a number, or the array/object structure is invalid, PostgreSQL raises an error. This error specifically indicates that a numeric extraction operation failed because no number was found at the specified JSON path. The error originates from the SQL/JSON path language implementation in PostgreSQL 12+ and becomes more common with PostgreSQL 17's new JSON_VALUE(), JSON_TABLE(), and enhanced JSON constructors.
Modify your SQL/JSON function to gracefully handle cases where a number isn't found:
-- Before (fails if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric);
-- After (returns NULL if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON ERROR);
-- With ON EMPTY clause (handles both missing and non-numeric)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON EMPTY NULL ON ERROR);Use lax mode (default) instead of strict mode for more forgiving path evaluation:
-- Lax mode (default): returns NULL/empty result if path doesn't match
SELECT jsonb_path_query(data, 'lax $.price');
-- Strict mode: raises error if path doesn't match
SELECT jsonb_path_query(data, 'strict $.price');Or verify that your JSON path expression matches your actual data structure:
-- Check the JSON structure first
SELECT data->'price', jsonb_typeof(data->'price') FROM my_table LIMIT 5;
-- Adjust path if needed
SELECT JSON_VALUE(data, '$.invoice.total' RETURNING numeric NULL ON ERROR);When using jsonb_path_query, jsonb_path_exists, or jsonb_path_match, add the silent parameter to suppress numeric errors:
-- With silent=true, suppresses numeric errors
SELECT jsonb_path_query(data, '$.price', vars => 'null'::jsonb, silent => true);
-- Alternatively, cast to text first if the value might not be numeric
SELECT jsonb_path_query_first(data, '$.price') AS raw_value;Ensure all JSON documents follow a consistent structure, or use conditional logic in your path:
-- Use COALESCE with multiple possible paths
SELECT COALESCE(
JSON_VALUE(data, '$.price' RETURNING numeric),
JSON_VALUE(data, '$.amount' RETURNING numeric),
0
) AS amount;
-- Or use CASE for complex logic
SELECT CASE
WHEN jsonb_typeof(data->'price') = 'number' THEN (data->'price')::numeric
ELSE NULL
END AS price;The "SQL/JSON number not found" error is part of PostgreSQL's SQL/JSON standard implementation introduced in PostgreSQL 12. In PostgreSQL 17+, this is more common because JSON_VALUE(), JSON_TABLE(), JSON_SERIALIZE(), and enhanced JSON/JSON_SCALAR constructors are now available with strict type coercion. If you're using prepared statements or ORMs, ensure they don't accidentally switch to strict mode. For jsonpath expressions, understand that .type() returns a string representation of the JSON type, and .size() only works on arrays—using these on non-matching paths in strict mode will also trigger similar errors. Consider using jsonb operators like -> and ->> for simpler cases, and reserve SQL/JSON functions for complex path queries where their error handling capabilities justify the overhead.
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