2203GERRORTier 2 — Caution✅ HIGH confidence

SQL/JSON item cannot be cast to target type

Category: Data ExceptionVersions: Postgres 14+

What this means

SQLSTATE 2203G is raised when a SQL/JSON item method attempts to cast a JSON value to a target type (e.g., .integer(), .double()) but the value cannot be represented as that type.

Why it happens

1Using a JSON path type method (.integer(), .double(), .string(), .boolean(), .date(), .time(), .timestamp()) on a JSON value that cannot be converted to the target type

How to reproduce

SQL/JSON type method on an incompatible value.

trigger — this will ERROR

SELECT jsonb_path_query('"not-a-number"'::jsonb, '$.integer()');

ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer

Fix 1: Ensure the JSON value is compatible with the target type before casting

When using SQL/JSON type methods.

fix

SELECT jsonb_path_query('42'::jsonb, '$.integer()'); -- valid

Why this works

Use jsonb_typeof() to confirm the JSON value type before applying type methods.

Version notes

Postgres 14+SQL/JSON type methods refined in Postgres 14.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE. Stable.