pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUse of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
4+ messages / 3 participants
[nested] [flat]
* Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
@ 2021-01-30 22:41 Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-30 22:46 ` Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Carlos Sotto Maior \(UOL\) @ 2021-01-30 22:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Use of an aggregate function on SELECT/GROUP By is a common need.
Also is common to use a friendly output-name like "cnt" for count(*).
But trying to filter results with HAVING <condition> will give SYNTAX error
if the output-name is used as argument.
Comment: Although column <cnt> does not exists in table it is present in the
Group By result that will be filtered by HAVING clause.
In other words: Postgresql is matching <HAVING> arguments to columns at
table level instead of matching to a group by result level.
Thanks for the attention.
Carlos Sotto Maior
--SELECT VERSION() ==> PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
-- Table: public.z_having
-- DROP TABLE public.z_having;
CREATE TABLE public.z_having
(
id integer NOT NULL,
f1 integer,
f2 integer,
payload character varying COLLATE pg_catalog."default",
active boolean,
CONSTRAINT pk_z_having_id PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE public.z_having OWNER to postgres;
COMMENT ON TABLE public.z_having
IS 'Use of Group By with count agregate with output-name (AS clause) is
OK\nBut If HAVING clause is used to filter on output-name (cnt) you get a
syntax error';
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (1, 10, 10,
'aaaaa', false);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (2, 10, 20,
'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (3, 10, 30,
'aaaaa', false);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (4, 10, 10,
'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (5, 10, 20,
'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (6, 10, 30,
'aaaaa', true);
-- GROUP BY WORKS OK --
SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
ORDER BY cnt DESC
-- Adding HAVING clause on output-name for the count() aggregate function
results in SYNTAXerror
SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
HAVING active = True AND cnt > 1
ORDER BY cnt DESC
--ERROR MESSAGE *******
--ERROR: column "cnt" does not exist
--LINE 5: HAVING active = True AND cnt > 1
^
--SQL state: 42703
--Character: 135
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
2021-01-30 22:41 Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
@ 2021-01-30 22:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-31 00:00 ` RE: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-01-30 22:46 UTC (permalink / raw)
To: Carlos Sotto Maior \(UOL\) <csotto@uol.com.br>; +Cc: pgsql-bugs@lists.postgresql.org
"Carlos Sotto Maior \(UOL\)" <csotto@uol.com.br> writes:
> In other words: Postgresql is matching <HAVING> arguments to columns at
> table level instead of matching to a group by result level.
That's per SQL standard. The fact that we allow the case in
GROUP BY is an anachronism. (You'll find also that we only allow it
for GROUP BY items that are *just* an output column name, not any
more-complicated expression. The equivalent restriction for HAVING
would make such a feature near useless anyway.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
2021-01-30 22:41 Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-30 22:46 ` Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-01-31 00:00 ` Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-31 11:33 ` Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 4+ messages in thread
From: Carlos Sotto Maior (UOL) @ 2021-01-31 00:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks a lot Tom.<br />
I'll solve it with a subquery.
<p>Carlos Sotto Maior (UOL)<br />
<a href="mailto:csotto@uol.com.br">csotto@uol.com.br</a></p>
<p> </p>
<hr />
<div><br />
<strong>De: </strong>"Tom Lane" <tgl@sss.pgh.pa.us><br />
<strong>Enviada: </strong>2021/01/30 19:46:37<br />
<strong>Para: </strong>csotto@uol.com.br<br />
<strong>Cc: </strong> pgsql-bugs@lists.postgresql.org<br />
<strong>Assunto: </strong> Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR<br />
</div>
"Carlos Sotto Maior \(UOL\)" <csotto@uol.com.br> writes:<br />
> In other words: Postgresql is matching <HAVING> arguments to columns at<br />
> table level instead of matching to a group by result level.<br />
<br />
That's per SQL standard. The fact that we allow the case in<br />
GROUP BY is an anachronism. (You'll find also that we only allow it<br />
for GROUP BY items that are *just* an output column name, not any<br />
more-complicated <span>expression</span>. The equivalent restriction for HAVING<br />
would make such a feature near useless anyway.)<br />
<br />
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
2021-01-30 22:41 Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-30 22:46 ` Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Tom Lane <tgl@sss.pgh.pa.us>
2021-01-31 00:00 ` RE: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
@ 2021-01-31 11:33 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 4+ messages in thread
From: Magnus Hagander @ 2021-01-31 11:33 UTC (permalink / raw)
To: Carlos Sotto Maior (UOL) <csotto@uol.com.br>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sun, Jan 31, 2021 at 1:01 AM Carlos Sotto Maior (UOL)
<csotto@uol.com.br> wrote:
>
> Thanks a lot Tom.
> I'll solve it with a subquery.
No need for a subquery. In your particular case, it should just work with:
SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
HAVING active = True AND count(*) > 1
ORDER BY cnt DESC
It can get ugly if your aggregates are large and complex, but not for
a simple count(*)...
//Magnus
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-01-31 11:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-30 22:41 Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-30 22:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-31 00:00 ` Carlos Sotto Maior (UOL) <csotto@uol.com.br>
2021-01-31 11:33 ` Magnus Hagander <magnus@hagander.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox