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

On Mon, Sep 21, 2026 at 12:19 PM Manuel Reyes Bravo
<manuelreyesbravo(at)gmail(dot)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 unknown
>
> Casting 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 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.
>

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)));

--
jian
https://www.enterprisedb.com/