public inbox for [email protected]
help / color / mirror / Atom feedERROR: failed to find conversion function from unknown to text
4+ messages / 2 participants
[nested] [flat]
* ERROR: failed to find conversion function from unknown to text
@ 2026-01-29 01:18 jian he <[email protected]>
2026-01-29 01:24 ` Re: ERROR: failed to find conversion function from unknown to text Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: jian he @ 2026-01-29 01:18 UTC (permalink / raw)
To: pgsql-hackers
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)
{
Oid inputBaseTypeId = 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/[email protected]
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch (1.1K, 2-v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch)
download | inline diff:
From a497d39fdd4aca2db190b21153e19093adb3dec7 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
2026-01-29 01:18 ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
@ 2026-01-29 01:24 ` Tom Lane <[email protected]>
2026-01-29 03:53 ` Re: ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2026-01-29 01:24 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
jian he <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
2026-01-29 01:18 ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
2026-01-29 01:24 ` Re: ERROR: failed to find conversion function from unknown to text Tom Lane <[email protected]>
@ 2026-01-29 03:53 ` jian he <[email protected]>
2026-04-22 06:33 ` Re: ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: jian he @ 2026-01-29 03:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Jan 29, 2026 at 9:24 AM Tom Lane <[email protected]> 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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
2026-01-29 01:18 ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
2026-01-29 01:24 ` Re: ERROR: failed to find conversion function from unknown to text Tom Lane <[email protected]>
2026-01-29 03:53 ` Re: ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
@ 2026-04-22 06:33 ` jian he <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: jian he @ 2026-04-22 06:33 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Jan 29, 2026 at 11:53 AM jian he <[email protected]> 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
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v2-0001-Disallow-UNKNOWN-target-types.patch (2.2K, 2-v2-0001-Disallow-UNKNOWN-target-types.patch)
download | inline diff:
From 9f3d8471db578d63729064d117ca2fed1abec112 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 22 Apr 2026 14:12:25 +0800
Subject: [PATCH v2 1/1] Disallow UNKNOWN target types
discussion: https://postgr.es/m/CACJufxHu0sXO8791FDcNXp2bFnE89jyuGkJbLCQkhgWq6XuNLg@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/
---
src/backend/parser/parse_coerce.c | 4 ++++
src/test/regress/expected/misc.out | 12 ++++++++++++
src/test/regress/sql/misc.sql | 6 ++++++
3 files changed, 22 insertions(+)
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 913ca53666f..aec75612ca1 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -3166,6 +3166,10 @@ find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId,
if (OidIsValid(targetTypeId))
targetTypeId = getBaseType(targetTypeId);
+ /* Disallow UNKNOWN target types. */
+ if (targetTypeId == UNKNOWNOID)
+ return COERCION_PATH_NONE;
+
/* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
index 6e816c57f1f..1c6ae0a3bbc 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,15 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+do $$ declare a text;
+begin a := 's'::text::unknown;
+end$$;
+ERROR: cannot cast type text to unknown
+LINE 1: a := 's'::text::unknown
+ ^
+QUERY: a := 's'::text::unknown
+CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment
+select 's'::text::unknown;
+ERROR: cannot cast type text to unknown
+LINE 1: select 's'::text::unknown;
+ ^
diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql
index 165a2e175fb..c568b4239f8 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -273,3 +273,9 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+
+do $$ declare a text;
+begin a := 's'::text::unknown;
+end$$;
+
+select 's'::text::unknown;
--
2.34.1
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-22 06:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-29 01:18 ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
2026-01-29 01:24 ` Tom Lane <[email protected]>
2026-01-29 03:53 ` jian he <[email protected]>
2026-04-22 06:33 ` jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox