Обсуждение: 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