How to Order By an Alias in PostgreSQL and Avoid the 'Column Not Exist' Error?

How to Order By an Alias in PostgreSQL: Resolving the "Column Not Exist" Error

In PostgreSQL, when working with aliases, you may encounter an error while ordering the results. For instance, consider the following query:

SELECT 
    title, 
    (stock_one + stock_two) AS global_stock
FROM
    product
ORDER BY
    global_stock = 0,
    title;

Executing this query in PostgreSQL 8.1.23 will likely produce the error: Query failed: ERROR: column "global_stock" does not exist. This error occurs because the alias "global_stock" is not recognized as an existing column in the "product" table.

To resolve this issue, you have a few options:

Option 1: Order By Position

PostgreSQL allows ordering by the position of the column instead of its name. For example, you can write:

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

This query orders the results first by the second column (which is "global_stock"), and then by the first column (which is "title").

Option 2: Wrap in a Subquery

Another method involves wrapping your original query in a subquery and using the CASE statement to handle the ordering:

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

This query first creates a subquery that calculates the "global_stock" value. The subquery is then wrapped in another SELECT statement that uses the CASE statement to order the results based on the availability of items (0 for available, 1 for unavailable).

The above is the detailed content of How to Order By an Alias in PostgreSQL and Avoid the 'Column Not Exist' Error?. For more information, please follow other related articles on the PHP Chinese website!