cannot cast type
Postgres could not find a cast pathway (implicit, explicit, or assignment) between the source and target types. The cast system looks up pg_cast and cannot locate a valid cast entry for the combination.
- 1Attempting to cast between two unrelated types with no registered cast (e.g., JSON directly to INTEGER)
- 2Trying an implicit cast that requires an explicit cast operator
- 3A custom type lacks a registered cast to the target type
- 4Attempting to cast an array type to a scalar type without an element-level cast
A JSON value is cast directly to INTEGER without an intermediate text step.
SELECT '42'::JSON::INTEGER;
expected output
ERROR: cannot cast type json to integer LINE 1: SELECT '42'::JSON::INTEGER;
Fix 1
Use an intermediate cast through TEXT
WHEN When converting between types that both have text representations.
SELECT ('42'::JSON)::TEXT::INTEGER; -- JSON -> TEXT -> INTEGER
-- For JSONB:
SELECT ('42'::JSONB)::TEXT::INTEGER;Why this works
Most Postgres types can cast to and from TEXT via their input/output functions. By routing through TEXT as an intermediate type, the cast system finds valid pathways (json->text and text->integer) even when a direct cast does not exist.
Fix 2
Use type-specific extraction functions
WHEN When extracting values from structured types like JSON or HSTORE.
-- Extract as text then cast: SELECT (data->>'price')::NUMERIC FROM products WHERE data->>'price' IS NOT NULL;
Why this works
->> extracts a JSONB field as TEXT (not JSON). The TEXT value then has a registered implicit cast to NUMERIC via the numeric input function, so the ::NUMERIC cast succeeds.
✕ Create a loose CAST function that silently returns NULL or 0 for all failures
Masks data quality problems; unexpected NULLs propagate silently through calculations.
src/backend/parser/parse_coerce.c — coerce_type()
Type Casting ↗CREATE CAST ↗Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev