Order by, expressions & column aliases issue - Mailing list pgsql-general

This isn't terribly clear in the documentation.

But it seems, in 8.0.3 and 8.1.1 that you cannot use a column alias
in an order by if you are using an expression (ie order by
my_column_alias <> 0 asc). If you use a plain asc/desc order by then
it is fine (ie order by my_column_alias asc).

Example:
create table tab1 ( x int );

insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (1);
insert into tab1(x) values (2);
insert into tab1(x) values (3);
insert into tab1(x) values (4);
insert into tab1(x) values (5);

-- order by column alias, single table. good.
select x as taco from tab1 order by taco desc;

-- order by x descending, with all zeros at end, using column name
select x as taco from tab1 order by x <> 0 desc, x desc;

-- same as above, but use alias
select x as taco from tab1 order by taco <> 0 desc, taco desc;

-- cleanup
drop table tab1;

Output:
CREATE TABLE
INSERT 423464319 1
INSERT 423464320 1
INSERT 423464321 1
INSERT 423464322 1
INSERT 423464323 1
INSERT 423464324 1
INSERT 423464325 1
INSERT 423464326 1
taco
------
     5
     4
     3
     2
     1
     0
     0
     0
(8 rows)

taco
------
     5
     4
     3
     2
     1
     0
     0
     0
(8 rows)

ERROR:  column "taco" does not exist
DROP TABLE

thanks
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/