Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL does not accept column alias in WHERE clause

In this pgexercises about joining 3 different tables, the answer is given as following:

select mems.firstname || ' ' || mems.surname as member, 
    facs.name as facility, 
    case 
        when mems.memid = 0 then
            bks.slots*facs.guestcost
        else
            bks.slots*facs.membercost
    end as cost
        from
                cd.members mems                
                inner join cd.bookings bks
                        on mems.memid = bks.memid
                inner join cd.facilities facs
                        on bks.facid = facs.facid
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and (
            (mems.memid = 0 and bks.slots*facs.guestcost > 30) or
            (mems.memid != 0 and bks.slots*facs.membercost > 30)
        )
order by cost desc;

Why can't I refer to the cost alias in the SELECT list in the WHERE clause?
If I run the same query with:

        ...
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and
        cost > 30
order by cost desc;

an error occurs:

ERROR: column "cost" does not exist

It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc; is allowed?

like image 687
Lamnk Avatar asked Jun 26 '16 16:06

Lamnk


People also ask

Can I use alias in WHERE clause Postgres?

We cannot use a column alias with WHERE and HAVING clauses.

Can column alias be used in WHERE clause?

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.

How do I specify a column alias?

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.

Can we use the SELECT statement column name alias in HAVING clause?

All parts of the query are parsed before it's executed. So technically, nothing prevents us from using select aliases in other clauses. Sorry but no, your theory is wrong.


1 Answers

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
like image 115
Erwin Brandstetter Avatar answered Sep 17 '22 15:09

Erwin Brandstetter