"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't know that this is covered all that well in the documentation.
It'd be better to do something about it than document it. The core of
the problem is that if we don't resolve the type of an unknown literal
while processing the sub-SELECT's target list, it doesn't work to try
to make a conversion later.
I think there's a rough consensus that it'd be okay to resolve unknown to
text at the time that the subquery is parsed, if there's no reason to
resolve it to something else. There would be cases where that's not
really what you want, but it would be unsurprising for it to act that way.
The hard part is that we've historically allowed
INSERT INTO sometab SELECT 'foo', ...
to resolve 'foo' as the type of sometab's first column (and I think this
is required by SQL spec, actually). So some work would have to be done
to not break that behavior. But I think this could be managed by
explicitly passing down knowledge of the INSERT's target column types into
the parsing of the sub-SELECT, and then the rule could be "resolve an
unknown SELECT output column to whatever target type is provided by
context, or to TEXT if the context provides no target".
regards, tom lane
