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).
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 |