Incident brief
Cannot coerce
A value was cast to a type with no defined conversion path. PostgreSQL refused the cast instead of guessing at one.
In 10 seconds
- What
- Cannot coerce
- What triggers it
- Cast a json value directly to a type with no defined json cast, such as integer.
- The fix
- Extract the specific field or value from the json first (with ->> or ->), then cast that extracted text.
- Proof
- Reproduced on PostgreSQL 16.14 → Casting a json object directly to integer was rejected with SQLSTATE 42846. The session was unaffected afterward.
The fix
What to do right now
The immediate, application-level response to this error.
- Extract the specific field or value from the json first (with ->> or ->), then cast that extracted text.
- Don't route the value through ::text as a generic bypass — see the counterexample for how that trades one error for a more confusing one.
- For scalar-only json values, casting via ::text can appear to work, but it isn't a reliable general pattern.
SELECT ('{"a":1}'::json->>'a')::integer;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — CREATE CAST
converts the integer constant 42 to type float8 by invoking a previously specified function, in this case float8(int4). (If no suitable cast has been defined, the conversion fails.)Read the full section on postgresql.org →
The unsupported cast
PostgreSQL has no cast defined from json straight to integer. Per the manual, if no suitable cast is defined, the conversion fails outright rather than being guessed at.Confirming the session still works
The failed cast didn't affect the session — the next statement ran normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Cast a json value directly to a type with no defined json cast, such as integer.
- 2PostgreSQL finds no cast from json to integer.
- 3SQLSTATE 42846 is reported, naming both types.
One client: cast a json value straight to integer.
-- No table needed: the failure happens purely at the type-cast stage.
SELECT 'no schema required' AS setup_note;SELECT '{"a":1}'::json::integer;SELECT 1 AS session_still_alive;What PostgreSQL actually returned
setup_note
--------------------
no schema required
(1 row)ERROR: cannot cast type json to integer
LINE 1: SELECT '{"a":1}'::json::integer;
^ session_still_alive
----------------------
1
(1 row)Extracting the field first, then casting the extracted text, was tested directly against the same json value that failed above.
Use the ->> operator to pull the field out as text, then cast that text to integer.
Without this
Above: casting the whole json object straight to integer is rejected.
With this, tested
Below: extracting the field first, then casting the extracted text, succeeds.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed