Menu
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?
816
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
Yes, you can certainly use column aliases in your "order by" clause.
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition]; The basic syntax of a column alias is as follows.
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.
180
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
