Re: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values

Please don’t top-post

On Wednesday, January 4, 2023, Sayyid Ali Sajjad Rizavi <sasrizavi(at)gmail(dot)com>
wrote:

> Breaking working queries for this is not acceptable.
>
>
> Good point, let's exclude Option 2.
>
>
>> This happens when possible so any remaining cases are not possible. Or,
>> at least apparently not worth the effort it would take to make work.
>
>
> Actually this doesn't happen when all of the values in that position are
> null. Or maybe I don't understand what you mean.
> If we don't consider the effort it would take to make it work, do you
> think Option 1 would be good to have? Because when I
> have an integer column in that position, I wouldn't want the unknown
> (null) values I supply to be resolved to `text` type.
>
>>
>>
The VALUES subquery has to produce its tabular output without being aware
of how the outer query is going to use it. The second column of your
values subquery lacks type information so the system chooses a default -
text.

Dealing with types is one of the harder medium-hard problems in computer
science…encountering this problem in real life has never seen me motivated
enough to gripe about it rather than just add an explicit cast and move
on. And I’ve been around long enough to know that the project is, and long
has been, aware of the dull pain points in this area.

David J.