DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 code 2203G occurs when a SQL/JSON path expression attempts to cast a JSON item into a target data type, but the value cannot be converted. This typically surfaces when using SQL/JSON functions like jsonb_path_query, jsonb_path_value, or the .integer(), .date(), .decimal() methods in path expressions. It is most commonly seen in PostgreSQL 12 and later versions.

Top 3 Causes

1. Non-numeric string value cast to a numeric type

When a JSON field contains a string like "abc" or "N/A" and you try to cast it to INTEGER or NUMERIC using a SQL/JSON path method, PostgreSQL cannot perform the conversion and throws 2203G.

-- Triggers 2203G
SELECT jsonb_path_query('{"price": "abc"}', '$.price.integer()');

-- Fix: Check type before casting
SELECT
  CASE
    WHEN jsonb_typeof(data->'price') = 'number'
    THEN (data->>'price')::INTEGER
    ELSE NULL
  END AS safe_price
FROM (SELECT '{"price": "abc"}'::jsonb AS data) sub;

-- Safe when value is actually a number
SELECT jsonb_path_query('{"price": 99}', '$.price.integer()');
-- Result: 99
Enter fullscreen mode Exit fullscreen mode

2. Non-ISO 8601 date string cast to DATE or TIMESTAMP

The SQL/JSON path .date() method strictly expects ISO 8601 format (YYYY-MM-DD). Any other format like "31/12/2023" or "December 31, 2023" will cause this error.

-- Triggers 2203G
SELECT jsonb_path_query(
  '{"created_at": "31/12/2023"}',
  '$.created_at.date()'
);

-- Fix: Extract as text and use to_date()
SELECT to_date(
  '{"created_at": "31/12/2023"}'::jsonb->>'created_at',
  'DD/MM/YYYY'
) AS converted_date;

-- Works correctly with ISO 8601
SELECT jsonb_path_query(
  '{"created_at": "2023-12-31"}',
  '$.created_at.date()'
);
-- Result: "2023-12-31"
Enter fullscreen mode Exit fullscreen mode

3. Attempting to cast a JSON array or object to a scalar type

Trying to apply .integer() or .text() directly to a JSON array or object will fail because SQL/JSON path methods expect a scalar item, not a composite structure.

-- Triggers 2203G
SELECT jsonb_path_query('{"scores": [10, 20, 30]}', '$.scores.integer()');

-- Fix: Target a specific index
SELECT jsonb_path_query(
  '{"scores": [10, 20, 30]}',
  '$.scores[0].integer()'
);
-- Result: 10

-- Fix: Iterate over all array elements
SELECT jsonb_path_query(
  '{"scores": [10, 20, 30]}',
  '$.scores[*].integer()'
);
-- Results: 10, 20, 30
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use jsonb_path_exists to guard before casting, and prefer jsonb_path_query_first for safer single-value extraction:

-- Safe extraction pattern
SELECT
  CASE
    WHEN jsonb_path_exists(data, '$.amount')
     AND jsonb_typeof(data->'amount') = 'number'
    THEN jsonb_path_query_first(data, '$.amount.integer()')::INTEGER
    ELSE NULL
  END AS safe_amount
FROM your_table;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enforce JSON structure with CHECK constraints — Validate data types at insert time so invalid values never reach your query layer.

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  CONSTRAINT chk_amount_numeric
    CHECK (jsonb_typeof(payload->'amount') = 'number'),
  CONSTRAINT chk_date_format
    CHECK (payload->>'txn_date' ~ '^\d{4}-\d{2}-\d{2}$')
);
Enter fullscreen mode Exit fullscreen mode

2. Always store dates in ISO 8601 format — Standardize date strings to YYYY-MM-DD or YYYY-MM-DDTHH:MM:SSZ at the application level before inserting into PostgreSQL. This ensures .date() and .timestamp() path methods work reliably without extra transformation logic.

Related Errors

  • 22P02 – invalid_text_representation: Similar casting failure but for standard PostgreSQL ::TYPE casts outside of SQL/JSON context.
  • 2203F – sql_json_array_not_found: Raised when a SQL/JSON path expects an array but finds a non-array item.
  • 2203W – sql_json_scalar_required: Raised when a scalar is required but an array or object is returned — closely related to cause #3 above.

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

Top comments (0)