CODEWITHSUNDEEP
mysqlsqlpostgresql
Chloe
Chloe

Reputation: 26264

Referring to a select aggregate column alias in the having clause in Postgres

I'm migrating from MySQL to Postgres. In MySQL I can use

select sum(clicks) c from table where event_date >= '1999-01-01' 
group by keyword_id 
having c > 10

Postgres gives an error

ERROR: column "c" does not exist

In Postgres I have to repeat the function in the having clause

select sum(clicks) c from table where event_date >= '1999-01-01' 
group by keyword_id 
having sum(clicks) > 10

There are a lot of places in the code that I have to change. Is there a setting in Postgres that will allow it to use column aliases in the having clause?

Upvotes: 15

Views: 7617

Answers (2)

Vivek S.
Vivek S.

Reputation: 21915

You can use WITH Queries (Common Table Expressions) to achieve the results like below

WITH t AS
  (SELECT sum(clicks) c
   FROM TABLE
   WHERE event_date >= '1999-01-01'
   GROUP BY keyword_id)
SELECT c
FROM t
WHERE c > 10

Upvotes: 10

Craig Ringer
Craig Ringer

Reputation: 324501

Is there a setting in Postgres that will allow it to use column aliases in the having clause?

No. Implementations that allow references to SELECT-list entries in HAVING are going outside the standard.

You should use a subquery, e.g.

select
  c
from (
  select 
    sum(clicks) c
  from table
  where event_date >= '1999-01-01'
  group by keyword_id 
) x
where c > 10;

... or repeat the aggregate.

Upvotes: 14

Related Questions