ORDER BY in lowercase output column

Question

I can run this Postgres request without any problems:

select
(select product_types.name from product_types
 where product_types.id = products.product_type_id) AS product_type_name
 from products
order by product_type_name

But when I tried to place the order in lower case, this would not work:

select
(select product_types.name from product_types
 where product_types.id = products.product_type_id) AS product_type_name
 from products
order by lower(product_type_name)

I get this error:

ERROR:  column "product_type_name" does not exist
LINE 4: order by lower(product_type_name)
                       ^

********** Error **********

ERROR: column "product_type_name" does not exist
SQL state: 42703
Character: 156

Can anyone shed me this light?

Answer 1

Quoting the man pageSELECT :

Each expression can be a name or serial number of the output column (SELECT list item), or it can be an arbitrary expression formed from the value of the input column.

You tried to order an expression formed from output -column, which is not possible.

Answer 2

:

select pt.name product_type_name from product_types pt
join products p on pt.id = p.product_type_id
order by pt.name

lower :

order by lower(pt.name)