Can you use a column alias in a HAVING clause? PostgreSQL says no. DuckDB, BigQuery, and ClickHouse each say yes differently. A tested comparison of four SQL engines.
Arsalan Noorafkan
Developer Advocate
TL;DR: A column alias is the name you give an expression in the SELECT list with AS. Whether that name is usable in HAVING depends on the engine. PostgreSQL never allows it. DuckDB allows it as a fallback, but never inside an aggregate function. BigQuery allows it and lets the alias override a same-named column from the FROM clause. ClickHouse allows it everywhere, including WHERE, because its aliases are global. The one form that works on all four is a fully qualified reference: HAVING MAX(ranked.ranking_page_count) >= 2.
It depends entirely on the engine, and the four most common analytics engines give four different answers.
The practical consequence: giving an aggregate the same name as the column it aggregates (MAX(x) AS x) and then referencing that name in HAVING produces a hard error on BigQuery and ClickHouse, and runs fine on PostgreSQL and DuckDB. Qualifying the reference with its table or CTE name (MAX(ranked.x)) works on all four.
| Query shape | PostgreSQL 16 | DuckDB 1.5 | BigQuery | ClickHouse 26 |
|---|---|---|---|---|
HAVING MAX(c) where c is also an alias for MAX(c) | runs, uses base column | runs, uses base column | error, nested aggregate | error, nested aggregate |
HAVING c with that same shadowing alias | error, c not grouped | runs, uses alias | runs, uses alias | runs, uses alias |
HAVING MAX(a) where a exists only as an alias | error, column does not exist | error, column not found | resolves to the alias, so it errors if the alias is itself an aggregate | error, nested aggregate |
Alias referenced in WHERE | not visible | visible | not visible | visible |
GROUP BY an alias that shadows a different column | column wins | column wins | alias wins | alias wins |
ORDER BY an alias that shadows a column | alias wins | alias wins | alias wins | alias wins |
HAVING MAX(table.c), fully qualified | runs | runs | runs | runs |
Verified August 13, 2026 on DuckDB 1.5.2, PostgreSQL 16.13, and ClickHouse 26.7. BigQuery rows come from the errors it returned on a live project plus its documented resolution rules.
Here is the query that started this. It came out of a Google Search Console report, trimmed down:
SELECT
query,
date,
device,
MAX(ranking_page_count) AS ranking_page_count
FROM ranked
GROUP BY 1, 2, 3
HAVING MAX(ranking_page_count) >= 2
ranked is a CTE that already has a column called ranking_page_count. The SELECT list creates an alias with the same name on top of MAX() of that column. Then HAVING uses the name a third time, inside another MAX().
Three uses of one name in one query. Nothing about it is exotic, and it is the shortest query I know that gets four different reactions from four engines.
I found out the way you would expect. It passed locally on DuckDB and would have failed the first time it ran in production. Here is what each engine actually does, ordered by how much authority it gives the alias.
PostgreSQL runs the query, and for the strictest possible reason. The SELECT documentation says each column in a HAVING condition "must unambiguously reference a grouping column, unless the reference appears within an aggregate function." Output aliases are simply not visible there, so MAX(ranking_page_count) can only mean the base column. There is no ambiguity to resolve.
Use the bare name instead and PostgreSQL tells you exactly what it thinks you wrote:
HAVING ranking_page_count >= 2
-- ERROR: column "ranked.ranking_page_count" must appear in the GROUP BY clause
PostgreSQL also documents the one alias inconsistency that the SQL standard is responsible for, and it is refreshingly blunt about it. GROUP BY prefers the input column: "In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name." ORDER BY does the opposite, and the docs add: "This inconsistency is made to be compatible with the SQL standard." Two clauses, opposite rules, on purpose.
DuckDB also runs the query and returns the right answer, which is why local testing waved it through.
DuckDB lists "column aliases in WHERE, GROUP BY, and HAVING" as a friendly SQL feature, which sounds like what BigQuery does. It isn't. In DuckDB the alias is a fallback rather than an override. MAX(ranking_page_count) binds the base column, that is a legal aggregate, and the alias never gets consulted.
Two probes pin down the rule. A name that exists only as an alias, used inside an aggregate, fails:
SELECT query, MAX(ranking_page_count) AS rpc
FROM ranked GROUP BY 1
HAVING MAX(rpc) >= 2
-- Binder Error: Referenced column "rpc" not found in FROM clause!
The same alias in a scalar expression resolves fine:
SELECT query, COUNT(*) AS n
FROM ranked GROUP BY 1
HAVING abs(n) >= 2 -- runs
So DuckDB refuses to push an alias into an aggregate function's arguments. That single decision is what makes a nested aggregate impossible to write by accident here.
BigQuery rejects the query:
Aggregations of aggregations are not allowed
The GoogleSQL query syntax documentation explains it in two sentences that live several screens apart. On scope: "Aliases in the SELECT list are visible only to the following clauses: GROUP BY clause, ORDER BY clause, HAVING clause." On precedence: "If a query contains aliases in the SELECT clause, those aliases override names in a FROM clause."
Override, not fall back. Inside HAVING, ranking_page_count is the alias, the alias is MAX(ranking_page_count), so the query BigQuery received was MAX(MAX(ranking_page_count)).
There is a carve-out, and it explains why this is easy to miss. A name is not ambiguous in GROUP BY, ORDER BY or HAVING "if it's both a column name and a SELECT list alias, as long as the name resolves to the same underlying object." Aliasing a column to its own name is harmless. Aliasing an aggregate of a column to that column's name is where it bites.
This is the same shape of problem as BigQuery's null handling in MERGE statements: a rule that is documented, defensible, and different enough from the neighbouring engine to cost you a deploy.
ClickHouse rejects the query too, with a different flavour of the same complaint:
Code: 184. DB::Exception: Aggregate function max(ranking_page_count) AS ranking_page_count
is found inside another aggregate function in query. (ILLEGAL_AGGREGATION)
The ClickHouse syntax documentation is upfront: "Aliases are global for a query or subquery, and you can define an alias in any part of a query for any expression." Then the warning: "Be careful with aliases that are the same as column or table names."
Global means global. ClickHouse substitutes the alias wherever the name appears, so it fails even when the alias shadows nothing at all:
SELECT query, max(ranking_page_count) AS rpc
FROM ranked GROUP BY query
HAVING max(rpc) >= 2 -- same ILLEGAL_AGGREGATION
ClickHouse is the only one of the four with a setting for this. prefer_column_name_to_alias = 1 makes column names beat aliases, and the original query then runs. Worth knowing if you are porting a large body of PostgreSQL or DuckDB SQL to ClickHouse. You cannot set it on the public play instance, which is read only, but it works locally.
It is tempting to sort this into two camps, alias-first and column-first, and that mostly holds. It is really four points on one axis: how much authority a SELECT alias gets. PostgreSQL gives it the least, none in HAVING and none in WHERE. DuckDB gives it a little, usable in three clauses but only when normal binding fails and never inside an aggregate. BigQuery gives it real precedence in GROUP BY, ORDER BY and HAVING. ClickHouse gives it the entire query.
Every one of those is defensible. They diverged because the SQL standard never specified aliases in HAVING, so there was no answer to converge on. Look at the comparison table again: the only row where all four agree is ORDER BY, which is exactly the clause the standard does define.
The row to worry about in a migration is GROUP BY, because that is where the disagreement can be silent. SELECT device AS query, COUNT(*) FROM ranked GROUP BY query groups by query on PostgreSQL and DuckDB, and by device on BigQuery and ClickHouse. Both of the first two raised an error in my test, so I caught it. Change the SELECT list slightly and you get two valid queries with two different grouping keys and no warning from anyone.
Qualify the column with its table or CTE name:
HAVING MAX(ranked.ranking_page_count) >= 2
Once the reference is ranked.ranking_page_count, no engine can read it as the alias. It runs on all four and returns identical rows. My two reports came back with the same 137 and 41 rows as before, so the fix preserves the semantics and only changes who can misread it.
Two habits avoid the whole category. Don't give an aggregate the same name as the column it aggregates, since MAX(x) AS max_x costs nothing and kills the ambiguity at the source. And qualify column references in HAVING whenever a CTE or table name is available. Qualifying looks redundant, so leave a comment explaining why, or someone will tidy it away.
| Error | Engine | Cause | Fix |
|---|---|---|---|
Aggregations of aggregations are not allowed | BigQuery | A HAVING or SELECT reference resolved to a SELECT alias that is itself an aggregate | Qualify the column, or rename the alias |
Code: 184 ... is found inside another aggregate function (ILLEGAL_AGGREGATION) | ClickHouse | Global alias substitution nested one aggregate inside another | Qualify the column, rename the alias, or set prefer_column_name_to_alias = 1 |
column "x" must appear in the GROUP BY clause | PostgreSQL | A bare name in HAVING bound to the base column, which is not grouped | Wrap it in the aggregate, or add it to GROUP BY |
column "x" does not exist | PostgreSQL | HAVING or WHERE referenced a SELECT alias, which is not in scope there | Repeat the expression instead of the alias |
Binder Error: Referenced column "x" not found in FROM clause! | DuckDB | An alias was used inside an aggregate function's arguments in HAVING | Use the underlying column inside the aggregate |
On BigQuery, ClickHouse, and DuckDB, yes. On PostgreSQL, no, because SELECT output aliases are not in scope in HAVING. The three that allow it do not agree on what happens when the alias shares a name with a column from the FROM clause, so alias references in HAVING are not portable.
Usually because a SELECT alias shadows the column it was built from. If you write MAX(x) AS x and then reference x in HAVING, BigQuery resolves the name to the alias, which expands to MAX(MAX(x)). Qualify the reference as MAX(table.x) or rename the alias.
An aggregate function ended up inside another aggregate function. Because ClickHouse aliases are global, this often happens through alias substitution rather than anything you wrote literally. It can fire even when the alias does not shadow a real column.
On DuckDB and ClickHouse, yes. On PostgreSQL and BigQuery, no. BigQuery's documentation is explicit that SELECT list aliases are visible only in GROUP BY, ORDER BY, and HAVING.
Alias precedence. When a name is both a base column and a SELECT alias, DuckDB binds the base column and BigQuery binds the alias. Developing on DuckDB and deploying to BigQuery hides this class of bug until deployment.
Yes, but with the opposite tie-break to ORDER BY. The docs state that an ambiguous GROUP BY name is read as an input-column name rather than an output column name, while ORDER BY reads it as the output column name, "to be compatible with the SQL standard."
It is legal everywhere and safe nowhere. MAX(x) AS x is the pattern behind every error in this post. A distinct name such as max_x removes the ambiguity before any engine has to guess.
For this specific issue, qualify column references in HAVING and avoid aliases that shadow columns. More generally, validate queries against the engine you deploy to rather than the one you develop on. BigQuery, Snowflake, and Redshift all support a dry run or explain that parses and type-checks a query without scanning data.
The lesson is not "learn the alias rules." I learned them and I will still forget the DuckDB one by November. It is that a query passing locally on DuckDB tells you very little about whether it parses on BigQuery or ClickHouse.
So I stopped treating local runs as verification. Every query in that project now gets a BigQuery dry run against typed stubs before it can merge, 43 statements in total, and it costs nothing because a dry run scans no bytes. It caught this bug the first time I ran it.
Parsing is only the first layer. A query can parse on the target engine and still return the wrong rows, which is what SQL unit tests are for: fixed input rows, an expected result, and a check that runs before merge.
If you develop on DuckDB and deploy to a warehouse, that is worth wiring into CI. Bruin runs the same asset definitions against BigQuery, PostgreSQL, ClickHouse, DuckDB and the rest, and bruin validate parses and type-checks every asset in a pipeline against its real platform, so "does this parse where it lands" becomes a question you can answer before a report breaks in front of someone.
A NULL in a join key never matches itself, so your incremental MERGE quietly duplicates rows and your joins quietly drop them. Here is why it happens, why the obvious null-safe fix (IS NOT DISTINCT FROM) turns your join into a cross join, and the OR-form that stays a hash join, with a full BigQuery benchmark.
Most batch ML scoring pipelines waste DS time pulling data into Python containers to do arithmetic the warehouse already does. Here is the trick, the receipt, and a copy-paste Jinja macro that translates an XGBoost model to SQL, validated on DuckDB.
What is a data app? Learn how data applications combine trusted data, business logic, and workflows, and how they differ from BI, dashboards, and reports.
Practical updates on open-source data pipelines, AI analysts, governance, and what we are shipping at Bruin.
The signup form is hosted by Brevo. Accept cookies to load it.