Re: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values
> > 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. On Thu, Jan 5, 2023 at 11:23 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, January 4, 2023, Sayyid Ali Sajjad Rizavi < > sasriz...@gmail.com> wrote: >> >> >> *Option 1:* Cast to the relevant column type in that position (to >> `integer` in this case), whenever we have an unknown type. >> > > 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. > > >> *Option 2:* Always give error if unknown type is not casted to desired >> type (`null::integer` will be necessary). >> > > Breaking working queries for this is not acceptable. > > David J. > >
Resolve UNKNOWN type to relevant type instead of text type while bulk update using values
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).
Re: [PATCH] Add .idea to gitignore for JetBrains CLion
I searched the commit fest app and there's already someone who has made this. On Sun, Dec 4, 2022 at 7:28 AM Sayyid Ali Sajjad Rizavi wrote: > From 6d10dafdd7c7789eddd7fd72ca22dfde74febe23 Mon Sep 17 00:00:00 2001 > From: Ali Sajjad > Date: Sun, 4 Dec 2022 06:03:11 -0800 > Subject: [PATCH] Add .idea to gitignore for JetBrains CLion > > --- > .gitignore | 1 + > 1 file changed, 1 insertion(+) > > diff --git a/.gitignore b/.gitignore > index 1c0f3e5e35..7118b90f25 100644 > --- a/.gitignore > +++ b/.gitignore > @@ -31,6 +31,7 @@ win32ver.rc > *.exe > lib*dll.def > lib*.pc > +**/.idea > > # Local excludes in root directory > /GNUmakefile > -- > 2.34.1 > > Probably I'm the first one building PostgreSQL on CLion. >
[PATCH] Add .idea to gitignore for JetBrains CLion
>From 6d10dafdd7c7789eddd7fd72ca22dfde74febe23 Mon Sep 17 00:00:00 2001 From: Ali Sajjad Date: Sun, 4 Dec 2022 06:03:11 -0800 Subject: [PATCH] Add .idea to gitignore for JetBrains CLion --- .gitignore | 1 + 1 file changed, 1 insertion(+) diff --git a/.gitignore b/.gitignore index 1c0f3e5e35..7118b90f25 100644 --- a/.gitignore +++ b/.gitignore @@ -31,6 +31,7 @@ win32ver.rc *.exe lib*dll.def lib*.pc +**/.idea # Local excludes in root directory /GNUmakefile -- 2.34.1 Probably I'm the first one building PostgreSQL on CLion.
Re: Enable pg_stat_statements extension for limited statements only
Yes, I agree that infrequent statements don't need stats. Actually I was distracted with the use case that I had in mind other than stats, maybe bringing that up will help. If someone's interested how frequent are deletes being run on a particular table, or what was the exact query that ran. Basically keeping track of queries. Although now I'm less convinced if a considerable amount of people will be interested in this, but let me know what you think. On Wed, Nov 30, 2022 at 10:15 AM Tom Lane wrote: > Sayyid Ali Sajjad Rizavi writes: > > Hi, I'd like to propose a change and get advice if I should work on it. > > The extension pg_stat_statements is very helpful, but the downside is > that > > it will take up too much disk space when storing query stats if it's > > enabled for all statements like SELECT, INSERT, UPDATE, DELETE. > > It will only take up a lot of disk space if you let it, by setting > the pg_stat_statements.max parameter too high. > > > For example, deletes do not happen too frequently; so I'd like to be able > > to enable pg_stat_statements only for the DELETE statement, maybe using > > some flags. > > I'm a little skeptical of the value of that. Why would you want stats > only for infrequent statements? > > I'm not denying that there might be usefulness in filtering what > pg_stat_statements will track, but it's not clear to me that > this particular proposal will be useful to many people. > > I wonder whether there would be more use in filters expressed > as regular expressions to match against the statement text. > That would allow, for example, tracking statements that mention > a particular table as well as statements with a particular > head keyword. I could see usefulness in both a positive filter > (must match this to get tracked) and a negative one (must not > match this to get tracked). > > regards, tom lane >
Allow round() function to accept float and double precision
Whenever rounding a number to a fixed number of decimal points in a calculation, we need to cast the number into a numeric before using round((col1/100.0)::numeric, 2). It would be convenient for everyone if round() also accepts float and double precision. Is this something I could work with? And is that feasible?
Enable pg_stat_statements extension for limited statements only
Hi, I'd like to propose a change and get advice if I should work on it. The extension pg_stat_statements is very helpful, but the downside is that it will take up too much disk space when storing query stats if it's enabled for all statements like SELECT, INSERT, UPDATE, DELETE. For example, deletes do not happen too frequently; so I'd like to be able to enable pg_stat_statements only for the DELETE statement, maybe using some flags. Another possibility is if we can limit the tables to which pg_stat_statements logs results.
