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
Hi jian,
jian he <jian.universality@gmail.com> wrote:
I choose to disallow UNKNOWN target types in find_coercion_pathway.
I reviewed v2 on master at 9e17d25e79d. It applies cleanly, builds
without warnings, and make check passes. The new test in misc fails
without the parse_coerce.c change (I applied only the test files to
master), so it does exercise the fix.
It does what Tom asked for: statements that reached the internal
"failed to find conversion function" elog now get a user-level error,
42846 instead of XX000. One problem, though. Besides the PL/pgSQL
assignment you mention, v2 rejects statements that work today, and
some of them can be stored. On master all of these succeed:
create table t (a text);
create view v1 as select cast(a as unknown)::text as c from t;
create view v2 as select cast(a as unknown) is null as c from t;
create index i on t ((cast(a as unknown)::text));
create table t_chk (a text check (cast(a as unknown) is not null));
create table t_def (a text default cast('x'::text as unknown)::text);
create function f() returns text
begin atomic select cast('x'::text as unknown)::text; end;
A pg_dump of that database restored onto v2 fails for all six objects
with "cannot cast type text to unknown". Since the CHECK and the
DEFAULT are part of CREATE TABLE, t_chk and t_def are missing after the
restore, and so is their data. pg_upgrade from such a cluster to v2
fails as well:
pg_restore: error: could not execute query: ERROR: cannot cast
type text to unknown
Casting to unknown on purpose is unlikely, but when it is there the
cost is a failed upgrade.
The same elog was item 6 in Alexander's "Internal error codes triggered
by regression tests and user queries, take 2" [1]/messages/by-id/apUcBVyazHEW5pcg@paquier.xyz, and it was left
aside because it was only reachable through unknownin(), an internal
input function. This thread shows it is also reachable with plain SQL:
a CAST to unknown, which is listed among the pseudo-types in the docs,
and from there through CREATE VIEW, CREATE TABLE AS and PREPARE.
So there is a narrower alternative to v2. Literals are handled at the
top of coerce_type(), so the only way to reach the "caller blew it"
elog from SQL is with an unknown input that is not a literal. Raising
ERRCODE_CANNOT_COERCE for that case only keeps the elog for real caller
bugs, and it can reuse "cannot cast type %s to %s", which parse_coerce.c
already uses four times and is translated. I tried it (attached as
.txt so the CF bot does not pick it up): the twelve statements in my
tests that give XX000 on master all get 42846, the statements that work
today keep working, the dump above restores with no errors, pg_upgrade
succeeds with the data in place, and make check passes.
The behavioral difference is that the cast itself stays legal and the
error is raised where the value is used:
master: failed to find conversion function from unknown to text
v2: cannot cast type text to unknown
narrower: cannot cast type unknown to text
If rejecting the cast outright is preferred, as v2 does, it would need
an answer for expressions that are already stored. I don't have a
strong opinion on which way is better, but these numbers seemed worth
having before choosing.
Two small things in v2: the new test lands at the end of misc.sql under
the "-- rewrite rules" heading, which it is unrelated to, and the
"commitfest entry:" line in the commit message is empty.
The SQL I used for the comparison is attached as well.
[1]: /messages/by-id/apUcBVyazHEW5pcg@paquier.xyz
Regards,
Manu
On Mon, Sep 21, 2026 at 12:19 PM Manuel Reyes Bravo
<manuelreyesbravo@gmail.com> wrote:
One problem, though. Besides the PL/pgSQL
assignment you mention, v2 rejects statements that work today, and
some of them can be stored. On master all of these succeed:create table t (a text);
create view v1 as select cast(a as unknown)::text as c from t;
create view v2 as select cast(a as unknown) is null as c from t;
create index i on t ((cast(a as unknown)::text));
create table t_chk (a text check (cast(a as unknown) is not null));
create table t_def (a text default cast('x'::text as unknown)::text);
create function f() returns text
begin atomic select cast('x'::text as unknown)::text; end;A pg_dump of that database restored onto v2 fails for all six objects
with "cannot cast type text to unknown". Since the CHECK and the
DEFAULT are part of CREATE TABLE, t_chk and t_def are missing after the
restore, and so is their data. pg_upgrade from such a cluster to v2
fails as well:pg_restore: error: could not execute query: ERROR: cannot cast
type text to unknownCasting to unknown on purpose is unlikely, but when it is there the
cost is a failed upgrade.
...
So there is a narrower alternative to v2. Literals are handled at the
top of coerce_type(), so the only way to reach the "caller blew it"
elog from SQL is with an unknown input that is not a literal. Raising
ERRCODE_CANNOT_COERCE for that case only keeps the elog for real caller
bugs, and it can reuse "cannot cast type %s to %s", which parse_coerce.c
already uses four times and is translated. I tried it (attached as
.txt so the CF bot does not pick it up): the twelve statements in my
tests that give XX000 on master all get 42846, the statements that work
today keep working, the dump above restores with no errors, pg_upgrade
succeeds with the data in place, and make check passes.The behavioral difference is that the cast itself stays legal and the
error is raised where the value is used:master: failed to find conversion function from unknown to text
v2: cannot cast type text to unknown
narrower: cannot cast type unknown to textIf rejecting the cast outright is preferred, as v2 does, it would need
an answer for expressions that are already stored. I don't have a
strong opinion on which way is better, but these numbers seemed worth
having before choosing.
Your approach (nocfbot-unknown-ereport-in-coerce_type.diff.txt) is
better than me.
create table t (a text);
create view v1 as select cast(a as unknown)::text as c from t;
Checking the target type and source type won't reveal that the above
statements involve a casts related to unknown.
Like the above case, casting to unknown is a transient process; both
the source and target types are text.
So https://git.postgresql.org/cgit/postgresql.git/commit/?id=1e7c4bb0049732ece651d993d03bb6772e5d281a
similar pg_upgrade check is not doable in here, I think.
Another issue is
src1=# select cast(NULL::text as unknown);
ERROR: cannot cast type unknown to text
LINE 1: select cast(NULL::text as unknown);
^
The error cursor points to the word "cast", the statement is "cast
text as unknown"
and the error message is "cannot cast type unknown to text".
So the whole thing is very confusing for regular users.
Maybe removing parser_coercion_errposition?
I guess I understand your comments, below are the comments I came up with.
What do you think?
/*
* Unknown-type literals were coerced above. A non-literal expression of
* type unknown, such as an explicit cast to unknown or a call to
* unknownin(), can still get here; since can_coerce_type() claims unknown
* coerces to anything, reject it here with a user-facing error.
*/
if (inputTypeId == UNKNOWNOID)
ereport(ERROR,
errcode(ERRCODE_CANNOT_COERCE),
errmsg("cannot cast type %s to %s",
format_type_be(inputTypeId),
format_type_be(targetTypeId)));
Hi jian,
Another issue is
src1=# select cast(NULL::text as unknown);
ERROR: cannot cast type unknown to text
...
The error cursor points to the word "cast", the statement is "cast
text as unknown"
and the error message is "cannot cast type unknown to text".
So the whole thing is very confusing for regular users.
Maybe removing parser_coercion_errposition?
I tried that on master (89829354de1), with the same query and ten
others that reach this error, before and after. The position turns
out to be the useful part. It points at the expression whose type is
unknown, which is what the user has to change:
select a, c, cast(b as unknown) as bad, c + 1 from t;
ERROR: cannot cast type unknown to text
LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t;
^
create view v as
select a,
c,
cast(b as unknown) as bad
from t;
ERROR: cannot cast type unknown to text
LINE 4: cast(b as unknown) as bad
^
Without parser_coercion_errposition() every case prints only the
ERROR line, so in a view like that there is nothing to say which
column it is.
What is confusing in your example is the "to text": that conversion
is the one that resolves the output column, and the user never wrote
it. A hint that says what to do covers that, and keeps the position:
ERROR: cannot cast type unknown to text
LINE 1: select cast(NULL::text as unknown);
^
HINT: Cast the expression to the type you want directly, not to
type unknown.
transformAssignedExpr() does the same for a type mismatch: it keeps
the position and adds "You will need to rewrite or cast the
expression."
I also tried an errdetail saying that only a literal of type unknown
can be converted to another type, and dropped it because it isn't
true: parameters of type unknown are converted too.
PREPARE p(unknown) AS SELECT $1::text works, before and after the
patch.
With the hint, make check passes (239), with no new warnings. One
case has no position with any wording, a UNION branch:
select a from t union all select cast(b as unknown) from t;
The set-operation coercion does not pass a location. That is how it
is today and I left it alone.
I guess I understand your comments, below are the comments I came up
with.
What do you think?
It reads well, and what it says about can_coerce_type() is right: it
does "if (inputTypeId == UNKNOWNOID) continue;". One thing I would
add, for the same reason as the errdetail above: parameters of type
unknown are also handled before this point, through
p_coerce_param_hook, so "Unknown-type literals and parameters were
coerced above."
The SQL and the output of each variant are attached.
Regards,
Manu