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.
What lands in your log
ERROR: cannot cast type json to integer
In 10 seconds
- What triggers it
- Cast a json value directly to a type with no defined json cast, such as integer.
- 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.
Fix
What to do right now
Application-level steps for 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;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Cannot coerce (42846) is statement-local: PostgreSQL does not retain the rejected value after the statement ends. Capture the exact bound value and statement position in application/server logs, then run this targeted validation before retrying.
Check whether PostgreSQL has a cast path
Validate the specific value/shape that can raise this SQLSTATE.
SELECT castsource::regtype AS source_type,
casttarget::regtype AS target_type,
castcontext, castmethod
FROM pg_cast
WHERE castsource = to_regtype('<source_type>')
AND casttarget = to_regtype('<target_type>');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
Because there's no cast path from json to integer, PostgreSQL refused the conversion before touching any data, session B's next statement is unaffected.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.
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.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
- Fix that trades one error for a more confusing one: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
The runbook for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Fix it — runbooks
Related errors
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.