PostgreSQL Cannot Coerce (SQLSTATE 42846)

PostgreSQL raises SQLSTATE 42846 (cannot_coerce) when it cannot find a valid cast path to convert a value from one data type to another. The error typically appears as:

ERROR:  cannot cast type <source_type> to <target_type>

For example: ERROR: cannot cast type integer to boolean. This error belongs to SQLSTATE class 42 (Syntax Error or Access Rule Violation) and is a hard compile-time or parse-time error — the query is rejected before execution begins.

What This Error Means

SQLSTATE class 42 covers errors detected during query parsing and semantic analysis. The 42846 condition specifically fires when PostgreSQL's type system cannot satisfy a required type coercion — either because no cast exists at all between the two types, or because an implicit cast is required in a context where only an implicit cast is allowed (such as inside an operator expression) but only an explicit cast has been defined.

PostgreSQL's type system distinguishes between three categories of casts: implicit casts (applied automatically by the planner without any explicit CAST or :: notation), assignment casts (applied automatically when assigning a value to a column), and explicit casts (only applied when the programmer uses CAST(x AS type) or x::type). When PostgreSQL needs an implicit cast but the catalog only records an explicit one, or when no cast entry exists at all, it raises cannot_coerce.

Because this error is caught at planning time, no transaction work is lost when it occurs — the statement simply fails to execute. Your connection remains usable and the transaction state (open or idle) is unchanged. You do not need to roll back unless you have other pending work you wish to abandon.

Common Causes

1. No cast defined between two types. PostgreSQL does not define casts between every pair of types. Attempting to use a composite type, a domain, or a custom type in a context that requires a different type, with no pg_cast entry between them, triggers this error.

2. Using ARRAY coercions without an element cast. Casting integer[] to boolean[] fails because while you might expect element-level casting to apply, PostgreSQL requires a cast defined for the array types themselves (or for the elements to have an applicable implicit cast).

3. Mixing enum types in expressions. Two different enum types defined with CREATE TYPE ... AS ENUM have no implicit cast between them. Using them interchangeably in a CASE expression, UNION, or function call where a single type is expected raises this error.

4. Composite or row type mismatches. Trying to cast one composite (row) type to another composite type is not supported unless a custom cast function is registered.

5. Driver or ORM binding a value with an incompatible type. Some JDBC, psycopg2, or ORM frameworks bind parameters with an inferred type that doesn't match the column. For example, binding a Python bool to an integer column via a library that generates a typed placeholder can surface this error.

How to Fix cannot_coerce

1. Add an explicit cast to a compatible intermediate type. If you need to move a value between two types, convert it through a type that has casts defined in both directions:

   -- Fails: no direct cast from integer to boolean
   SELECT 1::boolean;
   
   -- Works: cast through text
   SELECT '1'::boolean;
   
   -- Or use the standard comparison instead:
   SELECT (1 != 0);
   

2. Use CAST or :: notation when an explicit cast exists. If a cast is registered as explicit-only in pg_cast, you must use explicit syntax:

   -- Check whether a cast exists and its castcontext
   SELECT castsource::regtype, casttarget::regtype, castcontext
   FROM pg_cast
   WHERE castsource = 'mytype'::regtype
     AND casttarget = 'targettype'::regtype;
   -- castcontext = 'e' means explicit only
   -- castcontext = 'a' means assignment
   -- castcontext = 'i' means implicit
   

3. Register a custom cast if you control both types. If you have user-defined types and need coercion between them, create a cast function and register it:

   CREATE FUNCTION my_type_to_other(my_type) RETURNS other_type
   LANGUAGE sql STRICT IMMUTABLE AS $$
     SELECT $1.some_field::other_type;
   $$;
   
   CREATE CAST (my_type AS other_type)
     WITH FUNCTION my_type_to_other(my_type)
     AS IMPLICIT;
   

4. Fix enum type mismatches by using a common type. When a CASE expression or UNION mixes two enum types, convert them to text:

   -- Fails: status_a and status_b are different enum types
   SELECT CASE WHEN flag THEN status_a ELSE status_b END FROM t;
   
   -- Works: cast both arms to text
   SELECT CASE WHEN flag THEN status_a::text ELSE status_b::text END FROM t;
   

5. Fix driver/ORM type binding issues. If the error originates from an application query, check whether your driver is annotating a bind parameter with an incorrect OID. With psycopg2, you can wrap the value in an psycopg2.extensions.adapt() call or provide an explicit type annotation. With JDBC, use the appropriate setXxx() method matching the target column type.

Additional Information

• SQLSTATE 42846 has been present since early PostgreSQL versions (pre-8.0). The behavior of implicit vs. explicit cast contexts was formalized in PostgreSQL 8.3 when the type coercion rules were reorganized.
• Related SQLSTATE codes in class 42 include: 42883 (undefined_function) — raised when no function or operator matches the argument types; 42804 (datatype_mismatch) — raised when two sides of an operation must be the same type but are not; 42P18 (indeterminate_datatype) — raised when PostgreSQL cannot infer the type of an untyped literal.
• The pg_cast system catalog is the authoritative source on what casts exist. Querying it directly is the fastest way to confirm whether a cast path exists and what its context is.
• ORMs such as SQLAlchemy, Hibernate, and ActiveRecord generally manage type binding transparently, but raw query strings with ? or %s placeholders bypass type inference and can expose this error when column types are unusual.

Frequently Asked Questions

Why does SELECT 1::boolean fail in PostgreSQL? PostgreSQL does not define a cast from integer to boolean. This is intentional — in SQL, truthiness is not equivalent to non-zero integers the way it is in C or Python. Use an explicit comparison instead: SELECT (1 != 0) returns true.

How do I check if a cast exists between two types? Query the pg_cast catalog: SELECT * FROM pg_cast WHERE castsource = 'type1'::regtype AND casttarget = 'type2'::regtype;. If no rows are returned, no cast is registered and you must convert via an intermediate type or create a custom cast.

Can I make a cast implicit after the fact? Yes. If a cast is already registered as explicit-only (castcontext = 'e'), you can drop it and re-create it as implicit. However, be cautious — adding implicit casts can affect operator resolution and cause unexpected query plan changes or ambiguous function overload resolution elsewhere in your schema.

Does this error abort my transaction? No. SQLSTATE 42846 is a statement-level error caught during parsing. The failing statement is rejected and the error is returned to the client, but your transaction remains open and any prior work within it is preserved. You only need to issue a ROLLBACK if you choose not to continue with that transaction.