ERROR: failed to find conversion function from unknown to text
Hi.
select cast(NULL::text as unknown);
ERROR: failed to find conversion function from unknown to text
I found similar issues in [1]/messages/by-id/41E555DA.1060707@gmail.com and [2]/messages/by-id/65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com, 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]: /messages/by-id/41E555DA.1060707@gmail.com
[2]: /messages/by-id/65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com
Attachments:
v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patchtext/x-patch; charset=US-ASCII; name=v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patchDownload+10-1
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
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;
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