EC
2203G
PostgreSQLERRORNotableData ExceptionHIGH confidence

SQL/JSON item cannot be cast to target type

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
  1. 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
trigger — this will error
SELECT jsonb_path_query('"not-a-number"'::jsonb, '$.integer()');

expected output

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

Fix

Ensure the JSON value is compatible with the target type before casting

WHEN When using SQL/JSON type methods.

Ensure the JSON value is compatible with the target type before casting
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 documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors