How to use an ALIAS in a PostgreSQL ORDER BY clause?

Solution 1:

You can always ORDER BY this way:

select 
    title, 
    ( stock_one + stock_two ) as global_stock
from product
order by 2, 1

or wrap it in another SELECT:

SELECT *
from
(
    select 
        title, 
        ( stock_one + stock_two ) as global_stock
    from product
) x
order by (case when global_stock = 0 then 1 else 0 end) desc, title

Solution 2:

One solution is to use the position:

select  title, 
        ( stock_one + stock_two ) as global_stock
from product
order by 2, 1

However, the alias should work, but not necessarily the expression. What do you mean by "global_stock = 0"? Do you mean the following:

select  title, 
        ( stock_one + stock_two ) as global_stock
from product
order by (case when global_stock = 0 then 1 else 0 end) desc, title

Solution 3:

In case anyone finds this when googling for whether you can just ORDER BY my_alias: Yes, you can. This cost me a couple hours.

As the postgres docs state:

The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause.

So either this has been fixed since, or this question is specifically about the ORDER BY my_alias = 0, other_column syntax which I didn't actually need.