Thread

1. ERROR: failed to find conversion function from unknown to text

   jian he <jian.universality@gmail.com> — 2026-01-29T01:18:49Z

   Hi.
   
   select cast(NULL::text as unknown);
   ERROR:  failed to find conversion function from unknown to text
   
   I found similar issues in [1] and [2], and both have already been resolved.
   
   Looking at resolveTargetListUnknowns -> coerce_type, it seems it can cope with
   transforming a source expression from an Unknown Const to a Text Const. However,
   it cannot coerce other Unknown type expressions, such as COERCEVIAIO, to a Text
   Const.
   
   It can fail for real table data, not just constant literals, specifically when
   you try to cast a text column to an Unknown data type.
   While people generally don't do this, it is still possible.
   
   create table t(a text);
   select cast(a as unknown) from t;
   
   we don't need to worry about the domain over UNKNOWNOID, since it's not allowed.
   seems like coerce_type don't have logic handle targettype as UNKNOWNOID.
   in function coerce_type, right above find_coercion_pathway, we can add
   
       if (targetTypeId == UNKNOWNOID)
       {
           Oid            inputBaseTypeId = getBaseType(inputTypeId);
           TYPCATEGORY s_typcategory = TypeCategory(inputBaseTypeId);
   
           if (s_typcategory == TYPCATEGORY_STRING)
               return node;
       }
   
   to solve this issue.
   
   
   [1]: https://www.postgresql.org/message-id/flat/41E555DA.1060707%40gmail.com
   [2]: https://postgr.es/m/65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com
   
   
   
   --
   jian
   https://www.enterprisedb.com/
   

2. Re: ERROR: failed to find conversion function from unknown to text

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-01-29T01:24:19Z

   jian he <jian.universality@gmail.com> writes:
   > select cast(NULL::text as unknown);
   > ERROR:  failed to find conversion function from unknown to text
   
   Is there a good reason why that shouldn't be an error?
   I certainly don't like the cast pathway you suggest adding
   to make it not be one --- that seems likely to cause lots of
   not-very-desirable behaviors.
   
   			regards, tom lane
   
   
   
   

3. Re: ERROR: failed to find conversion function from unknown to text

   jian he <jian.universality@gmail.com> — 2026-01-29T03:53:39Z

   On Thu, Jan 29, 2026 at 9:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >
   > Is there a good reason why that shouldn't be an error?
   
   at that time, i want
   select cast('a'::text as unknown);
   
   behave the same as
   
   select cast('a' as unknown);
   
   To make it an error, meaning it's not possible to coerce to the unknown type.
   We can do it in the function find_coercion_pathway, just
   after the ``if (sourceTypeId == targetTypeId)`` check:
   
       if (targetTypeId == UNKNOWNOID)
           return COERCION_PATH_NONE;
   
   it's also doable in the function can_coerce_type,
   right after the ``if (inputTypeId == UNKNOWNOID)``:
   
           if (targetTypeId == UNKNOWNOID)
               return false;
   
   
   
   --
   jian
   https://www.enterprisedb.com/
   
   
   
   

4. Re: ERROR: failed to find conversion function from unknown to text

   jian he <jian.universality@gmail.com> — 2026-04-22T06:33:01Z

   On Thu, Jan 29, 2026 at 11:53 AM jian he <jian.universality@gmail.com> wrote:
   >
   > To make it an error, meaning it's not possible to coerce to the unknown type.
   > We can do it in the function find_coercion_pathway, just
   > after the ``if (sourceTypeId == targetTypeId)`` check:
   >
   >     if (targetTypeId == UNKNOWNOID)
   >         return COERCION_PATH_NONE;
   >
   > it's also doable in the function can_coerce_type,
   > right after the ``if (inputTypeId == UNKNOWNOID)``:
   >
   >         if (targetTypeId == UNKNOWNOID)
   >             return false;
   
   I choose to disallow UNKNOWN target types in find_coercion_pathway.
   
   do $$ declare a int;
   begin a := '1'::text::unknown;
   end$$;
   
   This DO statement does not cause any error in the HEAD, because of
   function find_coercion_pathway:
   ```
       /*
        * When parsing PL/pgSQL assignments, allow an I/O cast to be used
        * whenever no normal coercion is available.
        */
       if (result == COERCION_PATH_NONE &&
           ccontext == COERCION_PLPGSQL)
           result = COERCION_PATH_COERCEVIAIO;
   ```
   
   but will result error with the attached V2:
   ERROR:  cannot cast type text to unknown
   
   
   
   --
   jian
   https://www.enterprisedb.com/