4

I'm able to run this Postgres query without any issue:

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 order by lower case it doesn't 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 someone please shed me some light on this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user648198
  • 2,004
  • 4
  • 19
  • 26

2 Answers2

4

At first sight, your first query could be rewritten just this way:

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

Then, ordering with the lower function would mean just changing the order by to:

order by lower(pt.name)
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • 2
    Minor side note: Postgres discourages [omitting the keyword `AS` for *column* aliases](http://www.postgresql.org/docs/current/interactive/sql-select.html#AEN80734) (but not for table aliases). – Erwin Brandstetter Nov 29 '13 at 03:50
  • Probably it's worth mentioning that you could add index as well `CREATE INDEX idx_sth ON products (lower(name));` – bart Nov 30 '16 at 16:52
2

Quoting the manual page on SELECT:

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

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228