PostgreSQL 2203B Error: Causes and Solutions Complete Guide

PostgreSQL Error 2203B: sql_json_number_not_found

PostgreSQL error code 2203B (sql_json_number_not_found) is raised when a SQL/JSON path expression expects to find a numeric value but either encounters a non-numeric type (string, boolean, array, object) or finds no value at all. This error commonly surfaces when using functions like jsonb_path_query, JSON_VALUE, or numeric conversion methods such as .double() and .integer() within JSONPath expressions. It became more prominent in PostgreSQL 14+ as SQL-standard JSON functions were formally introduced and error codes were granularized.

---

Top 3 Causes

1. The Target JSON Field Contains a Non-Numeric Value

When a JSONPath expression uses numeric type methods (.double(), .integer()) against a field that holds a string or other non-numeric type, PostgreSQL immediately raises 2203B.

-- Error: field "price" is a string, not a number
SELECT jsonb_path_query(
    '{"price": "free"}'::jsonb,
    '$.price.double()'
);
-- ERROR: SQL/JSON number not found (SQLSTATE 2203B)

-- Fix: Check type before conversion
SELECT
    CASE
        WHEN jsonb_typeof(data->'price') = 'number'
        THEN (data->>'price')::numeric
        ELSE NULL
    END AS safe_price
FROM products;

-- Fix (PostgreSQL 16+): Use DEFAULT ON ERROR
SELECT JSON_VALUE(
    data,
    '$.price' RETURNING numeric
    DEFAULT NULL ON ERROR
)
FROM products;

2. The JSON Path Points to a Non-Existent Key

When a required key is missing from the JSON document and a numeric conversion is attempted on the resulting empty path, 2203B is triggered. This is especially common when JSON schemas evolve over time without corresponding query updates.

-- Error: "store" key is missing entirely
SELECT jsonb_path_query(
    '{"name": "widget"}'::jsonb,
    '$.store.price.double()'
);
-- ERROR: SQL/JSON number not found (SQLSTATE 2203B)

-- Fix: Use jsonb_path_query_first (returns NULL instead of error)
SELECT jsonb_path_query_first(
    '{"name": "widget"}'::jsonb,
    '$.store.price'
) AS price;
-- Result: NULL (no error)

-- Fix: Guard with key existence check
SELECT
    CASE
        WHEN data ? 'store' AND (data->'store') ? 'price'
             AND jsonb_typeof(data->'store'->'price') = 'number'
        THEN (data#>>'{store,price}')::numeric
        ELSE 0.00
    END AS price
FROM products;

3. Array Index Out of Bounds or Empty Array

Accessing a specific array index that doesn't exist (e.g., an empty array) while expecting a numeric result will produce this error.

-- Error: items array is empty, index 0 doesn't exist
SELECT jsonb_path_query(
    '{"items": []}'::jsonb,
    '$.items[0].qty.integer()'
);
-- ERROR: SQL/JSON number not found (SQLSTATE 2203B)

-- Fix: Check array length first
SELECT
    CASE
        WHEN jsonb_array_length(data->'items') > 0
             AND jsonb_typeof(data->'items'->0->'qty') = 'number'
        THEN (data->'items'->0->>'qty')::integer
        ELSE 0
    END AS first_qty
FROM orders;

-- Fix: Use jsonb_path_query_array to safely extract all numeric quantities
SELECT jsonb_path_query_array(
    data,
    '$.items[*].qty ? (@.type() == "number")'
) AS all_quantities
FROM orders;

---

Quick Fix Solutions

Use this reusable PL/pgSQL helper to safely extract numeric values without risking 2203B in production:

CREATE OR REPLACE FUNCTION safe_json_number(
    p_data jsonb,
    p_path text
) RETURNS numeric AS $$
DECLARE
    v_result jsonb;
BEGIN
    v_result := jsonb_path_query_first(p_data, p_path::jsonpath);
    IF v_result IS NULL OR jsonb_typeof(v_result) != 'number' THEN
        RETURN NULL;
    END IF;
    RETURN (v_result #>> '{}')::numeric;
EXCEPTION
    WHEN SQLSTATE '2203B' THEN RETURN NULL;
    WHEN OTHERS THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Usage
SELECT safe_json_number('{"discount": 15}'::jsonb, '$.discount');
-- Result: 15

SELECT safe_json_number('{"discount": "none"}'::jsonb, '$.discount');
-- Result: NULL (no error)

---

Prevention Tips

Enforce numeric type at insert time with CHECK constraints:

ALTER TABLE products
ADD CONSTRAINT chk_price_is_number
CHECK (
    (data->'price') IS NULL
    OR jsonb_typeof(data->'price') = 'number'
);

Always prefer jsonb_path_query_first or jsonb_path_query_array over plain jsonb_path_query when dealing with optional or schema-flexible JSON fields. These variants return NULL or [] instead of raising errors, making your queries far more resilient in production environments. Pair them with COALESCE for safe default values and wrap any critical JSON numeric extraction logic in a PL/pgSQL EXCEPTION WHEN SQLSTATE '2203B' handler to prevent cascading failures across your application.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.