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

From: Sayyid Ali Sajjad Rizavi <sasrizavi(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values
Date: 2023-01-05 06:10:50
Message-ID: CAHxW8BAdvHQEQf6KizMg3H=yQVW1Nurv6ZK1OnRQfdGrt=i=sA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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).

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2023-01-05 06:15:41 Re: Fix showing XID of a spectoken lock in an incorrect field of pg_locks view.
Previous Message Amit Kapila 2023-01-05 06:04:37 Re: wake up logical workers after ALTER SUBSCRIPTION