MIME-Version: 1.0
From: jian he <jian.universality@gmail.com>
Date: Thu, 29 Jan 2026 09:18:49 +0800
Message-ID: <CACJufxHu0sXO8791FDcNXp2bFnE89jyuGkJbLCQkhgWq6XuNLg@mail.gmail.com>
Subject: ERROR: failed to find conversion function from unknown to text
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000bd0bb406497ca55f"
Archived-At: <https://www.postgresql.org/message-id/CACJufxHu0sXO8791FDcNXp2bFnE89jyuGkJbLCQkhgWq6XuNLg%40mail.gmail.com>
Precedence: bulk

--000000000000bd0bb406497ca55f
Content-Type: text/plain; charset="UTF-8"

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/65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com


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

--000000000000bd0bb406497ca55f
Content-Type: text/x-patch; charset="US-ASCII"; 
	name="v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch"
Content-Disposition: attachment; 
	filename="v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mkyrlq6a0>
X-Attachment-Id: f_mkyrlq6a0

RnJvbSBhNDk3ZDM5ZmRkNGFjYTJkYjE5MGIyMTE1M2UxOTA5M2FkYjNkZWM3IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFRodSwgMjkgSmFuIDIwMjYgMDk6MTA6MDIgKzA4MDAKU3ViamVjdDogW1BBVENIIHYx
IDEvMV0gRVJST1I6ICBmYWlsZWQgdG8gZmluZCBjb252ZXJzaW9uIGZ1bmN0aW9uIGZyb20KIHVu
a25vd24gdG8gdGV4dAoKZGVtbzoKY3JlYXRlIHRhYmxlIHQoYSB0ZXh0KTsKc2VsZWN0IGNhc3Qo
YSBhcyB1bmtub3duKSBmcm9tIHQ7CgpkaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tLwot
LS0KIHNyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9jb2VyY2UuYyB8IDEwICsrKysrKysrKysKIDEg
ZmlsZSBjaGFuZ2VkLCAxMCBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQv
cGFyc2VyL3BhcnNlX2NvZXJjZS5jIGIvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2NvZXJjZS5j
CmluZGV4IDkxM2NhNTM2NjZmLi42OGFjOTllNDcxYyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
cGFyc2VyL3BhcnNlX2NvZXJjZS5jCisrKyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9jb2Vy
Y2UuYwpAQCAtNDExLDYgKzQxMSwxNiBAQCBjb2VyY2VfdHlwZShQYXJzZVN0YXRlICpwc3RhdGUs
IE5vZGUgKm5vZGUsCiAJCX0KIAkJcmV0dXJuIHJlc3VsdDsKIAl9CisKKwlpZiAodGFyZ2V0VHlw
ZUlkID09IFVOS05PV05PSUQpCisJeworCQlPaWQJCQlpbnB1dEJhc2VUeXBlSWQgPSBnZXRCYXNl
VHlwZShpbnB1dFR5cGVJZCk7CisJCVRZUENBVEVHT1JZIHNfdHlwY2F0ZWdvcnkgPSBUeXBlQ2F0
ZWdvcnkoaW5wdXRCYXNlVHlwZUlkKTsKKworCQlpZiAoc190eXBjYXRlZ29yeSA9PSBUWVBDQVRF
R09SWV9TVFJJTkcpCisJCQlyZXR1cm4gbm9kZTsKKwl9CisKIAlwYXRodHlwZSA9IGZpbmRfY29l
cmNpb25fcGF0aHdheSh0YXJnZXRUeXBlSWQsIGlucHV0VHlwZUlkLCBjY29udGV4dCwKIAkJCQkJ
CQkJCSAmZnVuY0lkKTsKIAlpZiAocGF0aHR5cGUgIT0gQ09FUkNJT05fUEFUSF9OT05FKQotLSAK
Mi4zNC4xCgo=
--000000000000bd0bb406497ca55f--