Resolve iso-8859-1 type to relevant type instead of text type while bulk update using values - Mailing list pgsql-hackers

From Sayyid Ali Sajjad Rizavi
Subject Resolve iso-8859-1 type to relevant type instead of text type while bulk update using values
Date
Msg-id CAHxW8BAdvHQEQf6KizMg3H=yQVW1Nurv6ZK1OnRQfdGrt=i=sA@mail.gmail.com
Whole thread Raw
Responses Re: Resolve iso-8859-1 type to relevant type instead of text type while bulk update using values
List pgsql-hackers
Hi !
I discovered an interesting behavior in PostgreSQL bulk update query using `from (values %s)` syntax.

Let's see an example;
```
update persons p
set age = t.age
from  (
    values
        ('uuid1', null),
        ('uuid2', null)
) as t(id, age)
where p.id = t.id;
```
The `age` column is of type integer. The above query will give this error: "age" is of type integer but expression is of type text. (PostgreSQL resolves the type as a text).

But if we change the values to these;
```
values
        ('uuid1', 21),
        ('uuid2', null)
```
We won't get any error because PostgreSQL will detect that at least one integer value exists in the 2nd position, so let's resolve this guy to `integer`.

The issue here is that it's very unexpected behavior which might succeed in most of the cases and fail in one case. This behavior can be seen in the `parser/parse_coerce.c` file.
```
     /*
      * 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.
      */
     if (ptype == UNKNOWNOID)
         ptype = TEXTOID;
```

So here are the 2 options I suggest:
Option 1: Cast to the relevant column type in that position (to `integer` in this case), whenever we have an unknown type.
Option 2: Always give error if unknown type is not casted to desired type (`null::integer` will be necessary).

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: wake up logical workers after ALTER SUBSCRIPTION
Next
From: Masahiko Sawada
Date:
Subject: Re: Fix showing XID of a spectoken lock in an incorrect field of pg_locks view.