PostgreSQL 2203B Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2203B: sql json number not found

PostgreSQL error 2203B (sql_json_number_not_found) occurs when a SQL/JSON path expression expects a numeric value at a specific path but finds something else — a string, boolean, null, or a missing key entirely. This error is commonly triggered by JSON path functions such as jsonb_path_query(), jsonb_path_exists(), and numeric-specific path methods like .abs(), .floor(), and .ceiling(). Understanding this error is critical for anyone working with semi-structured JSONB data in PostgreSQL 12+.

Top 3 Causes

1. The value exists but is the wrong type (string instead of number)

This is the most frequent culprit. When a JSON field contains "150" (a string) instead of 150 (a number), any numeric path operation will fail.

-- Triggers ERROR 2203B: price is a string, not a number
SELECT jsonb_path_query('{"price": "150"}', '$.price.abs()');

-- Safe fix: use a type filter in the path expression
SELECT jsonb_path_query('{"price": 150}', '$.price ? (@ is number).abs()');

-- Safe extraction with type check
SELECT (data ->> 'price')::numeric
FROM products
WHERE jsonb_typeof(data -> 'price') = 'number';

2. The key is missing or explicitly null

When the referenced key does not exist in the JSON document or is set to null, PostgreSQL cannot locate the expected number and raises this error — especially in strict mode.

-- Triggers ERROR 2203B: key 'amount' is missing
SELECT jsonb_path_query('{"name": "widget"}', 'strict $.amount + 10');

-- Fix: use lax mode (default) and COALESCE for safety
SELECT COALESCE(
    jsonb_path_query_first(data, 'lax $.amount')::numeric,
    0
) AS safe_amount
FROM orders;

-- Fix: check key existence before operating
SELECT *
FROM orders
WHERE data ? 'amount'
  AND jsonb_typeof(data -> 'amount') = 'number';

3. Numeric path methods applied without a type guard

Methods like .abs(), .floor(), and .ceiling() are strictly for numbers. Calling them without first verifying the value type leads directly to 2203B.

-- Triggers ERROR 2203B: 'delta' is a string
SELECT jsonb_path_query('{"delta": "N/A"}', '$.delta.abs()');

-- Fix: add a type filter before the method
SELECT jsonb_path_query(
    '{"delta": -42}',
    '$.delta ? (@ is number).abs()'
);

-- Fix: safe batch query with CASE guard
SELECT
    id,
    CASE
        WHEN jsonb_typeof(data -> 'score') = 'number'
        THEN jsonb_path_query_first(data, '$.score.floor()')::integer
        ELSE NULL
    END AS safe_score
FROM results;

Quick Fix Solutions

Use these patterns as drop-in replacements to eliminate 2203B errors:

-- Pattern 1: Always use lax mode + type filter
SELECT jsonb_path_query(data, 'lax $.price ? (@ is number)')
FROM products;

-- Pattern 2: Null-safe extraction
SELECT jsonb_path_query_first(data, 'lax $.amount')::numeric
FROM orders;

-- Pattern 3: Filter rows before path operations
SELECT jsonb_path_query(data, '$.value.abs()')
FROM metrics
WHERE jsonb_typeof(data -> 'value') = 'number';

Prevention Tips

Enforce types at insert time with CHECK constraints:

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

Always include ? (@ is number) filters in path expressions that use numeric methods. Make this a team coding standard. Using lax mode explicitly and combining it with type-guard filters ensures your queries degrade gracefully to NULL rather than throwing runtime errors — making your application far more resilient to inconsistent JSON input from external APIs or ETL pipelines.

Related Errors

Code	Name	Description
`2203A`	`sql_json_array_not_found`	Array expected but not found at path
`2203C`	`sql_json_object_not_found`	Object expected but not found at path
`2203F`	`sql_json_scalar_required`	Scalar required but array/object found
`22032`	`invalid_json_text`	Malformed JSON string, fails before path evaluation

📖 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.

DEV Community