ERROR: failed to find conversion function from unknown to text
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t53204psql -h localhost -U postgresBuilt from patchset v4 (message #4), September 20, 2026 at 01:01 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t53204_4 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t53204_4 && git checkout t53204_4Patchset v4 (message #4) is on t53204_4
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