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

On Fri, Jan 6, 2023 at 8:28 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> writes:
> > we cast a quoted value to UNKNOWN type, but this is a special value
> > null which can be casted to any SQL data type. Probably we could add a
> > ANYNULLTYPE or some such generic null type which can be casted to any
> > data type. Then a null value without any type is labeled as
> > ANYNULLTYPE if specific type information is not available.
>
> And ... how does that differ from the existing behavior of UNKNOWN?
>

From the below comment
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/

A constant null can be coerced to be null of any data type. So it
doesn't need to be coerced to text or anything for the reason
mentioned in the comment. Using UNKNOWN type, we have problem of not
being able to coerce it to another type. But ANYNULLVALUE can be
coerced to anything and thus can continue to be used till a point
where we know the data type it needs to be coerced to.

--
Best Wishes,
Ashutosh Bapat