PostgreSQL Refuses Select List Names in HAVING

A report of repeat customers names its count of orders n in the select list and filters on that name in HAVING. SQLite, MySQL and MariaDB run it, and PostgreSQL refuses the query.

PostgreSQL looks up names in HAVING among the table’s columns only, so HAVING n > 1 fails when n is the name of COUNT(*).

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER);
INSERT INTO t VALUES (1), (1), (2);

the example

SELECT a, COUNT(*) AS n FROM t GROUP BY a HAVING n > 1;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT a, COUNT(*) AS n FROM t GROUP BY a HAVING COUNT(*) > 1;

Repeat the aggregate: HAVING COUNT(*) > 1.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:50: error: SQLite, MySQL and MariaDB let HAVING use a name given in the select list; PostgreSQL refuses it. [alias-in-having]
    n
    Repeat the aggregate: HAVING COUNT(*) > 1.

1 problem: 1 error.

baresql rules alias-in-having prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.