// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
cannot cast type integer to uuid

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 integer to uuid, boolean to date or jsonb to integer[].
  • Casting a composite or array value to a scalar type.
  • Code ported from another database that allows the conversion.

How to fix it

  1. Convert through text when the text form is valid input for the target type.

    SELECT 42::text::numeric;
    SELECT ('{"n": 7}'::jsonb ->> 'n')::int;
  2. 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);
  3. 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

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.

// fewer errors, faster fixes

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.