PostgreSQL 42846 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42846: cannot coerce

PostgreSQL error 42846 (cannot coerce) occurs when the database engine cannot convert a value from one data type to another, either implicitly or explicitly. This happens when no valid cast path exists in PostgreSQL's internal cast catalog (pg_cast) between the source and target types. Unlike a simple type mismatch, this error specifically means the coercion mechanism itself is unavailable for the given type pair.

Top 3 Causes and Fixes

1. Direct CAST Between Incompatible Types

PostgreSQL does not define cast paths for every type combination. Attempting to cast directly between types like integer → boolean or json → uuid will trigger 42846.

-- ❌ Causes 42846: no direct cast from integer to boolean
SELECT CAST(1 AS boolean);

-- ✅ Fix: route through an intermediate type (text)
SELECT CAST(CAST(1 AS text) AS boolean);

-- ✅ Fix: use a conditional expression instead
SELECT CASE WHEN some_int_col <> 0 THEN true ELSE false END
FROM your_table;

-- ✅ Fix: cast json field to uuid via text
SELECT CAST(payload->>'user_id' AS uuid)
FROM events;

-- Check if a cast path exists before using it
SELECT EXISTS (
    SELECT 1 FROM pg_cast
    WHERE castsource = 'integer'::regtype
      AND casttarget = 'boolean'::regtype
) AS cast_available;

2. Missing Cast Between Custom or Domain Types

When using user-defined composite types or domain types, PostgreSQL requires an explicitly registered cast function — structural similarity is not enough.

-- ❌ Causes 42846: no cast defined between two custom domains
CREATE DOMAIN celsius AS numeric;
CREATE DOMAIN fahrenheit AS numeric;

-- Attempting implicit conversion fails without a registered cast
-- ✅ Fix: create a conversion function and register it as a CAST
CREATE OR REPLACE FUNCTION celsius_to_fahrenheit(celsius)
RETURNS fahrenheit AS $$
    SELECT ($1 * 9.0 / 5.0 + 32)::fahrenheit;
$$ LANGUAGE sql STRICT IMMUTABLE;

CREATE CAST (celsius AS fahrenheit)
    WITH FUNCTION celsius_to_fahrenheit(celsius)
    AS ASSIGNMENT;

-- Now this works correctly
SELECT CAST(100::celsius AS fahrenheit); -- returns 212

3. Implicit Cast Failure in Function or Operator Arguments

PostgreSQL tries implicit casting when function argument types don't match exactly. If no implicit-level cast exists for the type pair, error 42846 is raised.

-- ❌ Causes 42846: passing xml type to a text-expecting function
SELECT length(xml_column) FROM documents;

-- ✅ Fix: use xmlserialize to explicitly convert xml to text
SELECT length(xmlserialize(content xml_column AS text))
FROM documents;

-- ✅ Fix: explicit CAST to satisfy the function signature
SELECT length(CAST(xml_column AS text))
FROM documents;

-- Inspect the cast context for any type pair
-- 'e' = explicit only, 'a' = assignment, 'i' = implicit
SELECT
    src.typname AS source,
    tgt.typname AS target,
    c.castcontext AS context
FROM pg_cast c
JOIN pg_type src ON src.oid = c.castsource
JOIN pg_type tgt ON tgt.oid = c.casttarget
WHERE src.typname = 'xml';

Quick Prevention Tips

1. Enforce type consistency at design time.
Use the same base types or shared domain types for related columns (especially foreign keys). Avoid relying on implicit casting in application queries — always be explicit about type conversions.

-- Use a shared domain type across tables to prevent mismatches
CREATE DOMAIN entity_id AS uuid NOT NULL;

CREATE TABLE users   (id entity_id DEFAULT gen_random_uuid() PRIMARY KEY);
CREATE TABLE orders  (user_id entity_id REFERENCES users(id));

2. Validate cast paths in CI/CD before deploying queries.
Query pg_cast in your staging environment to confirm that the required cast path exists before deploying to production. If a needed cast is missing, register it early rather than working around it at runtime.

-- Use this in pre-deployment validation scripts
SELECT src.typname, tgt.typname, c.castcontext
FROM pg_cast c
JOIN pg_type src ON src.oid = c.castsource
JOIN pg_type tgt ON tgt.oid = c.casttarget
WHERE src.typname = 'your_source_type'
  AND tgt.typname = 'your_target_type';

Related Errors

42883 — undefined_function: Often appears alongside 42846 when no function overload matches the given argument types.
42804 — datatype_mismatch: Raised when two expressions in a context requiring equal types (e.g., UNION, CASE) have different types.
22P02 — invalid_text_representation: The cast path exists, but the actual value is not valid for the target type at runtime.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community