PostgreSQL 42846: cannot coerce
An explicit cast between two types that have no conversion defined, such as integer to uuid or boolean to date.
- SQLSTATE
42846- Name
cannot_coerce
What it means
PostgreSQL only casts between types when a cast is defined for that pair. 42846 (cannot_coerce) means you asked for one with ::type or CAST and none exists, so PostgreSQL can't convert the value no matter what it contains.
This differs from 22P02, where a cast exists but the particular text value is invalid. Often the fix is to go through text, or to use a function that builds the target type properly.
Common causes
- Casting between unrelated types, such as
integertouuid,booleantodateorjsonbtointeger[]. - Casting a composite or array value to a scalar type.
- Code ported from another database that allows the conversion.
How to fix it
-
Convert through text when the text form is valid input for the target type.
SELECT 42::text::numeric; SELECT ('{"n": 7}'::jsonb ->> 'n')::int; -
Use a conversion function that builds the target type.
SELECT to_timestamp(1700000000); SELECT ARRAY(SELECT jsonb_array_elements_text('[1,2,3]'::jsonb)::int); -
List the casts that do exist from a type.
SELECT castsource::regtype, casttarget::regtype, castcontext FROM pg_cast WHERE castsource = 'integer'::regtype;
Reproduce it safely
SELECT 1::uuid; -- ERROR: cannot cast type integer to uuid
Related errors
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42803column must appear in the GROUP BY clause42830invalid foreign key42P18could not determine data type of parameter42703column does not exist42P01relation does not exist42P02there is no parameter
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.