Failure to coerce unknown type to specific type - Mailing list pgsql-bugs

Original report and patch by Karl Schnaitter.

create table a(u) as select '1';
create table b(i int);
insert into b select u from a;
ERROR:  failed to find conversion function from unknown to integer

SELECT a=b FROM (SELECT ''::text, '  ') x(a,b);
ERROR:  failed to find conversion function from unknown to text

The strange thing about these cases are that can_coerce_type returns
true, but coerce_type fails.

This can be fixed by a small change (attached) to find_coercion_pathway to add:

    else if (sourceTypeId == UNKNOWNOID)
        result = COERCION_PATH_COERCEVIAIO;

I don't see any big problem with doing this, because we've already
done the type inference; it's just a question of whether we succeed or
fail when we try to apply it. I don't see any reason to fail a cast
from unknown to something else.

However, this still doesn't fix a more complex case like:

create table tt(x text primary key);
create table ut(x unknown, foreign key(x) references tt);
insert into tt values('');
insert into ut values('');
update ut set x = x;

Regards,
     Jeff Davis