DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42846 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42846: cannot coerce

The PostgreSQL error code 42846 (cannot coerce) occurs when the database engine is unable to convert a value from one data type to another, either implicitly or explicitly. This typically means that no valid cast path exists in PostgreSQL's pg_cast system catalog between the source and target types. You'll commonly encounter this error in queries involving mismatched column types, function calls with wrong argument types, or improper use of the CAST operator.

Top 3 Causes

1. Casting Between Incompatible Types Directly

Not every type pair has a registered cast in PostgreSQL. Attempting a direct cast between types that have no defined conversion path will immediately trigger error 42846.

-- This will fail: no direct cast from json to xml
SELECT CAST('{"key": "value"}'::json AS xml);
-- ERROR: cannot cast type json to xml

-- Fix: route through an intermediate type (text)
SELECT CAST(('{"key": "value"}'::json)::text AS xml);

-- Check available cast paths from a given type
SELECT
    pg_catalog.format_type(castsource, NULL) AS source_type,
    pg_catalog.format_type(casttarget, NULL) AS target_type,
    CASE castcontext
        WHEN 'i' THEN 'implicit'
        WHEN 'a' THEN 'assignment'
        WHEN 'e' THEN 'explicit only'
    END AS context
FROM pg_catalog.pg_cast
WHERE pg_catalog.format_type(castsource, NULL) = 'json';
Enter fullscreen mode Exit fullscreen mode

2. Passing Wrong Argument Types to Functions

Functions in PostgreSQL have strictly typed signatures. When you pass an argument of the wrong type and no implicit cast exists, the engine raises 42846 instead of silently converting the value.

-- Problematic: passing text where double precision is expected
SELECT to_timestamp('1700000000');
-- May raise: cannot coerce type text to double precision

-- Fix: explicitly cast the argument
SELECT to_timestamp(1700000000::double precision);

-- Another common case with user-defined functions
CREATE FUNCTION get_discount(price integer) RETURNS numeric AS $$
    SELECT price * 0.9;
$$ LANGUAGE sql;

-- Wrong: passing text
SELECT get_discount('100');  -- may fail depending on implicit cast availability

-- Correct: explicit cast
SELECT get_discount('100'::integer);
SELECT get_discount(CAST('100' AS integer));
Enter fullscreen mode Exit fullscreen mode

3. Missing Cast Definition for Custom or Domain Types

When you define custom composite types or domain types, PostgreSQL does not automatically generate cast paths to or from other types. Any query that tries to coerce between these types without a registered cast will fail.

-- Define a custom domain
CREATE DOMAIN us_zip AS text
    CHECK (VALUE ~ '^\d{5}(-\d{4})?$');

-- This may fail without an explicit cast definition
SELECT CAST(some_zip_column AS text) FROM addresses;

-- Fix: create a cast function and register it
CREATE OR REPLACE FUNCTION zip_to_text(us_zip)
RETURNS text AS $$
    SELECT $1::text;
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE CAST (us_zip AS text)
    WITH FUNCTION zip_to_text(us_zip)
    AS IMPLICIT;

-- Now this works seamlessly
SELECT 'SW1A1AA'::us_zip::text;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use intermediate types: If a direct cast is unavailable, go through text as a bridge type — most types can cast to and from text.
  • Always use explicit casting: Replace implicit coercions with CAST(x AS type) or x::type syntax to make type handling transparent and reliable.
  • Check pg_cast first: Before writing complex type conversion logic, query pg_cast to confirm the cast path actually exists. 
-- Universal quick check: does a cast path exist?
SELECT EXISTS (
    SELECT 1 FROM pg_cast
    WHERE castsource = 'json'::regtype
      AND casttarget = 'xml'::regtype
) AS cast_exists;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Validate type compatibility at schema design time. When introducing custom types, domain types, or composite types, always define the necessary CREATE CAST entries in your migration scripts. Document which cast paths your application depends on and include them in code reviews.

  2. Never rely on implicit coercions in production SQL. Implicit casts can disappear between PostgreSQL major versions or under different configuration settings. Enforce explicit CAST usage in your team's SQL style guide to avoid subtle breakage during upgrades.

📖 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.

Top comments (0)