PostgreSQL does not accept column alias in WHERE clause

Solution 1:

You ask two questions:
1.

Why can't I refer to the SELECT cost alias at the WHERE clause?

2.

But why order by cost desc; is allowed?

The manual has an answer for both of them here:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

It's defined by the SQL standard and the reason is the sequence of events in a SELECT query. At the time WHERE clauses are applied, output columns in the SELECT list have not yet been computed. But when it comes to ORDER BY, output columns are readily available.

So while this is inconvenient and confusing at first, it still kind of makes sense.

Related:

• PostgreSQL Where count condition
• Best way to get result count before LIMIT was applied