Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR

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