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

[jian he]

On Thu, Jan 29, 2026 at 9:24 AM Tom Lane  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/

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

[Tom Lane]

jian he  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

ERROR: failed to find conversion function from unknown to text

[jian he]

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)
{
OidinputBaseTypeId = 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/65937bea0901052223w162a977dyeaaf888a854f7...@mail.gmail.com



--
jian
https://www.enterprisedb.com/
From a497d39fdd4aca2db190b21153e19093adb3dec7 Mon Sep 17 00:00:00 2001
From: jian he 
Date: Thu, 29 Jan 2026 09:10:02 +0800
Subject: [PATCH v1 1/1] ERROR:  failed to find conversion function from
 unknown to text

demo:
create table t(a text);
select cast(a as unknown) from t;

discussion: https://postgr.es/m/
---
 src/backend/parser/parse_coerce.c | 10 ++
 1 file changed, 10 insertions(+)

diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 913ca53666f..68ac99e471c 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -411,6 +411,16 @@ coerce_type(ParseState *pstate, Node *node,
 		}
 		return result;
 	}
+
+	if (targetTypeId == UNKNOWNOID)
+	{
+		Oid			inputBaseTypeId = getBaseType(inputTypeId);
+		TYPCATEGORY s_typcategory = TypeCategory(inputBaseTypeId);
+
+		if (s_typcategory == TYPCATEGORY_STRING)
+			return node;
+	}
+
 	pathtype = find_coercion_pathway(targetTypeId, inputTypeId, ccontext,
 	 &funcId);
 	if (pathtype != COERCION_PATH_NONE)
-- 
2.34.1