42846ERRORTier 1 — Safe✅ HIGH confidence

cannot cast type

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

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.

Why it happens

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

How to reproduce

A JSON value is cast directly to INTEGER without an intermediate text step.

trigger — this will ERROR

SELECT '42'::JSON::INTEGER;

ERROR: cannot cast type json to integer LINE 1: SELECT '42'::JSON::INTEGER;

Fix 1: Use an intermediate cast through TEXT

When converting between types that both have text representations.

fix

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 extracting values from structured types like JSON or HSTORE.

fix

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

What not to do

✗

Create a loose CAST function that silently returns NULL or 0 for all failures

Why it's wrong: Masks data quality problems; unexpected NULLs propagate silently through calculations.

Sources

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

📚 Feature docs: https://www.postgresql.org/docs/current/typeconv-casts.html

🔧 Source ref: src/backend/parser/parse_coerce.c — coerce_type()

📖 Further reading: Type Casting

📖 Further reading: CREATE CAST

Confidence assessment

✅ HIGH confidence

Stable and well-documented. The cast system is consistent across versions. Edge case: JSONB added a text cast in Postgres 9.4; earlier JSON type versions had fewer built-in casts.