PostgreSQL 2203G Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2203G: sql json item cannot be cast to target type

PostgreSQL error 2203G occurs when a SQL/JSON function attempts to convert a JSON item into a specified target SQL type, but the conversion is not possible. This typically happens with functions like JSON_VALUE(), JSON_QUERY(), and JSON_TABLE() when the actual JSON value's format or type is incompatible with the requested RETURNING type.

Top 3 Causes

1. Type Mismatch Between JSON Value and Target Type

The most common cause. A JSON string like "hello" cannot be cast to integer, and a date stored as a plain string may not directly convert to a date type via the RETURNING clause.

-- This will raise ERROR 2203G
SELECT JSON_VALUE(
    '{"score": "not-a-number"}'::jsonb,
    '$.score' RETURNING integer
);

-- Fix: Extract as text first, then cast explicitly
SELECT JSON_VALUE(
    '{"score": "42"}'::jsonb,
    '$.score' RETURNING text
)::integer;

-- Fix: Use ON ERROR to handle gracefully
SELECT JSON_VALUE(
    '{"score": "not-a-number"}'::jsonb,
    '$.score' RETURNING integer
    NULL ON ERROR
);
-- Returns: NULL instead of raising an error

2. Numeric Value Out of Range for Target Type

JSON numbers have virtually unlimited precision, but PostgreSQL types like integer (max ~2.1 billion) and real have strict limits. Trying to fit an oversized number into a smaller type triggers this error.

-- ERROR: value too large for integer
SELECT JSON_VALUE(
    '{"user_id": 9999999999}'::jsonb,
    '$.user_id' RETURNING integer
);

-- Fix: Use bigint or numeric for large numbers
SELECT JSON_VALUE(
    '{"user_id": 9999999999}'::jsonb,
    '$.user_id' RETURNING bigint
);
-- Returns: 9999999999

-- Fix: Use numeric for high-precision decimals
SELECT JSON_VALUE(
    '{"rate": 1.123456789012345678}'::jsonb,
    '$.rate' RETURNING numeric
);

3. Attempting to Cast a JSON Array or Object to a Scalar Type

JSON_VALUE() is designed to return scalar values only. If your jsonpath expression resolves to an array or an object, it cannot be cast to text, integer, or any other scalar type.

-- ERROR: array/object cannot be cast to scalar
SELECT JSON_VALUE(
    '{"tags": ["pg", "sql", "json"]}'::jsonb,
    '$.tags' RETURNING text
);

-- Fix: Use JSON_QUERY() for arrays and objects
SELECT JSON_QUERY(
    '{"tags": ["pg", "sql", "json"]}'::jsonb,
    '$.tags'
);
-- Returns: ["pg", "sql", "json"]

-- Fix: Target a specific array element
SELECT JSON_VALUE(
    '{"tags": ["pg", "sql", "json"]}'::jsonb,
    '$.tags[0]' RETURNING text
);
-- Returns: pg

-- Fix: Expand array to rows using JSON_TABLE
SELECT tag
FROM JSON_TABLE(
    '{"tags": ["pg", "sql", "json"]}'::jsonb,
    '$.tags[*]'
    COLUMNS (tag text PATH '$')
) AS jt;

Quick Fix Solutions

Always use ON ERROR: Add NULL ON ERROR or DEFAULT value ON ERROR to catch cast failures without crashing your query.
Extract as text first: Pull the value as text using RETURNING text or the ->> operator, then cast with ::target_type at the SQL level where you have full control.
Match type sizes: Use bigint instead of integer and numeric instead of real when you are uncertain about value ranges.
Use JSON_QUERY() for non-scalars: Switch from JSON_VALUE() to JSON_QUERY() whenever your path may return an array or object.

Prevention Tips

Validate JSON schema at insert time using CHECK constraints or application-level validation to ensure values are stored in the correct format and range before they ever reach a JSON_VALUE() call.

CREATE TABLE events (
    id serial PRIMARY KEY,
    payload jsonb NOT NULL,
    CONSTRAINT chk_event_count_range CHECK (
        (payload->>'count') IS NULL OR
        (payload->>'count')::numeric BETWEEN 0 AND 2147483647
    )
);

Adopt defensive query patterns across your team by standardizing the use of ON ERROR clauses and always testing jsonpath expressions against representative sample data — including edge cases like nulls, empty arrays, and large numbers — before deploying to production.

📖 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